Assigning query output to variable in postgres stored proc-CodePudding

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

CodePudding user response：

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;