How to write non parameterized function that returns table in postgresql

Question

create or replace function demowhile_()
    returns table (id integer,name varchar(50))
        language 'plpgsql'
       as $$

    declare id integer;
    declare name varchar(50);

    begin
    CREATE temporary TABLE demo_(id integer,name varchar(50));
    CREATE temporary TABLE temp_ (id integer);
        insert into temp_ (select id from demo);

            while (select count(*) from temp_) <> 0
            loop
                id = (select top(1) id from temp_);
                name = (select name from demo where id = id);
                insert into demo_ values (id,name);

                delete from temp_ where id = id;

                end loop;

                return query select id,name from demo;
                end;
                $$;

****while executing it is giving error like ****

select * from demowhile();

**ERROR:  column reference "id" is ambiguous
LINE 1: insert into temp_ (select id,name from demo)
                                  ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  insert into temp_ (select id,name from demo)
CONTEXT:  PL/pgSQL function demowhile() line 9 at SQL statement
SQL state: 42702**

just rename your variable id = (select top(1) id from temp_); to for example _id on all places where you actually want to use it — JosMac
– JosMac, Commented Feb 22, 2019 at 12:44
The whole approach seems unnecessarily complicated. As far as I can tell this could be done with one simple SQL statement. No loops, not "top", not PL/pgSQL - especially because at the end you just return the contents of the demo table that wasn't changed by the inefficient and slow row-by-row processing in the loop. What is the real problem are you trying to solve with that? — user330315
– user330315, Commented Feb 22, 2019 at 13:22

404 · Accepted Answer · 2019-02-22 13:12:44Z

1

demowhile_() returns table (id integer,name varchar(50) is basically the same thing as demowhile_(out id integer, out name varchar(50)) returns setof record

The id output column can be referenced directly from within the function. You've then also created a function variable named id. So when you assign a value to id, are you assigning to the output parameter or the declared variable? PG doesn't know, so you get that error.

And of course the simple solution is to give each a unique name.

answered Feb 22, 2019 at 13:12

404

8,7522 gold badges33 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

How to write non parameterized function that returns table in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related