PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement

by

How are you executing that function? It works as a select statement.

Create a table: public.users

create table public.users (id int, firstname varchar, lastname varchar);

Insert some records:

insert into public.users values (1, 'aaa','bbb'),(2,'ccc','ddd');

function: my_function

CREATE OR REPLACE FUNCTION my_function(user_id integer) RETURNS TABLE(id integer, firstname character varying, lastname character varying) AS $$
    DECLARE
        ids INTEGER[];
    BEGIN
         ids := ARRAY[1,2];
         RETURN QUERY
             SELECT users.id, users.firstname, users.lastname
             FROM public.users
             WHERE users.id = ANY(ids);
    END;
$$ LANGUAGE plpgsql;

Now you can use with *

select * from my_function(1);

Result of query

 id | firstname | lastname 
----+-----------+----------
  1 | aaa       | bbb
  2 | ccc       | ddd

Or with column names as well

select id,firstname,lastname from my_function(1);

Result 

 id | firstname | lastname 
----+-----------+----------
  1 | aaa       | bbb
  2 | ccc       | ddd

More Related Contents:

  1. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  2. Function to loop through and select data from multiple tables
  3. INSERT with dynamic table name in trigger function
  4. The forgotten assignment operator “=” and the commonplace “:=”
  5. Truncating all tables in a Postgres database
  6. Unnest multiple arrays in parallel
  7. How to set value of composite variable field using dynamic SQL
  8. How do I do large non-blocking updates in PostgreSQL?
  9. Call a set-returning function with an array argument multiple times
  10. Is there a way to disable function overloading in Postgres
  11. Record returned from function has columns concatenated
  12. Can I make a plpgsql function return an integer without using a variable?
  13. PostgreSQL function returning multiple result sets
  14. Dynamic SQL (EXECUTE) as condition for IF statement
  15. Returning from a function with OUT parameter
  16. SELECT raises exception in PL/pgSQL function
  17. Test for null in function with varying parameters
  18. Return SETOF rows from PostgreSQL function
  19. Error when setting n_distinct using a plpgsql variable
  20. Dynamically generate columns for crosstab in PostgreSQL
  21. SQL Sub queries in check constraint
  22. Cursor based records in PostgreSQL
  23. ERROR: unterminated quoted string at or near
  24. How can I send some http request from postgresql function or trigger
  25. Select from a table variable
  26. Hashing a String to a Numeric Value in PostgreSQL
  27. Loop on tables with PL/pgSQL in Postgres 9.0+
  28. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  29. Loop through columns of RECORD
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment