Assigning query output to variable in postgres stored proc

Question

I am trying to assign a variable the result of a query in a postgres stored procedure. Here is what I am trying to run:

CREATE OR Replace PROCEDURE schema.MyProcedure() 
AS $$

DECLARE 
    RowCount int = 100;
    
BEGIN
    
    select cnt into RowCount 
    from (
        Select count(*) as cnt
        From schema.MyTable
        ) ;
    
    RAISE NOTICE 'RowCount: %', RowCount;
    
END;
$$
LANGUAGE plpgsql;

schema.MyTable is just some arbitrary table name but the script is not displaying anything, not even the random value I assigned RowCount to (100).

What am I doing wrong?

Thanks

You either either need to use an inout parameter to return the value, or use a function instead of a procedure to return a value. — user9706
– user9706, Commented Sep 24, 2021 at 18:22
I dont need the RowCount returned, I was just using Raise Notice to check if the true table's row count was being assigned to the RowCount variable. — JimmyBuffet_Express
– JimmyBuffet_Express, Commented Sep 24, 2021 at 18:27
Works for me, though I did have to add an alias for the subquery: ...From schema.MyTable) as ct, to CREATE the procedure. call myprocedure (); NOTICE: RowCount: 68. Also I would avoid using mixed case identifiers. Best guess is you are not calling the procedure you think you are. — Adrian Klaver
– Adrian Klaver, Commented Sep 24, 2021 at 18:27

Anthony Sotolongo · Accepted Answer · 2021-09-24 18:30:02Z

2

You need an alias for the subquery, for example : as sub

CREATE OR Replace PROCEDURE schema.MyProcedure() 
AS $$

DECLARE 
    RowCount int = 100;
    
BEGIN
    
    select cnt into RowCount 
    from (
        Select count(*) as cnt
        From schema.MyTable
        ) as sub ;
    
    RAISE NOTICE 'RowCount: %', RowCount;
    
END;
$$
LANGUAGE plpgsql;

answered Sep 24, 2021 at 18:30

Anthony Sotolongo

1,6882 gold badges13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adrian Klaver Over a year ago

Though it could be simplified to: Select count(*) into RowCount from ... and eliminate the sub query.

NGG · Accepted Answer · 2021-10-02 09:25:01Z

1

You can also assign any variable with a query result in parenthesis.

CREATE OR REPLACE PROCEDURE schema.my_procedure()
AS
$$
DECLARE
    row_count BIGINT;
BEGIN

    row_count = (SELECT COUNT(*) FROM schema.my_table);

    RAISE NOTICE 'RowCount: %', row_count;

END;
$$ LANGUAGE plpgsql;

You should use BIGINT instead of INT.

And it's far better to write your code and table definition with snake_case style as possible.

answered Oct 2, 2021 at 9:25

NGG

151 bronze badge

Collectives™ on Stack Overflow

Assigning query output to variable in postgres stored proc

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related