Postgres function returning table not returning data in columns

by

To get individual columns instead of the row type, call the function with:

SELECT * FROM testfunction();

Just like you would select all columns from a table.
Also consider this reviewed form of your test function:

CREATE OR REPLACE FUNCTION testfunction()
  RETURNS TABLE(a int, b int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _a int := 0;
   _b int := 0;
BEGIN
   CREATE TABLE tempTable AS SELECT _a, _b;
   RETURN QUERY SELECT * FROM tempTable;
   DROP TABLE tempTable;
END
$func$;

In particular:

The DECLARE key word is only needed once.

Avoid declaring parameters that are already (implicitly) declared as OUT parameters in the RETURNS TABLE (...) clause.

Don’t use unquoted CaMeL-case identifiers in Postgres. It works, unquoted identifiers are cast to lower case, but it can lead to confusing errors. See:

The temporary table in the example is completely useless (probably over-simplified). The example as given boils down to:

CREATE OR REPLACE FUNCTION testfunction(OUT a int, OUT b int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   a := 0;
   b := 0;
END
$func$;

More Related Contents:

  1. PL/pgSQL checking if a row exists
  2. How to perform a select query in a DO block?
  3. Refactor a PL/pgSQL function to return the output of various SELECT queries
  4. Is SELECT or INSERT in a function prone to race conditions?
  5. Difference between language sql and language plpgsql in PostgreSQL functions
  6. How to return result of a SELECT inside a function in PostgreSQL?
  7. Pass multiple values in single parameter
  8. GROUP BY and aggregate sequential numeric values
  9. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  10. PostgreSQL parameterized Order By / Limit in table function
  11. PostgreSQL IF statement
  12. Call a set-returning function with an array argument multiple times
  13. Compute percents from SUM() in the same SELECT sql query
  14. Postgres analogue to CROSS APPLY in SQL Server
  15. Record returned from function has columns concatenated
  16. Postgres Dynamic Query Function
  17. Return setof record (virtual table) from function
  18. Window Functions or Common Table Expressions: count previous rows within range
  19. How to pass custom type array to Postgres function
  20. How to find a table having a specific column in postgresql
  21. DROP FUNCTION without knowing the number/type of parameters?
  22. Return multiple columns and rows from a function PostgreSQL instead of record
  23. COPY with dynamic file name
  24. Check if NULL exists in Postgres array
  25. pseudo_encrypt() function in plpgsql that takes bigint
  26. How to use SQL LIKE condition with multiple values in PostgreSQL?
  27. How to create a new database with the hstore extension already installed?
  28. Postgres trigger after insert accessing NEW
  29. Join a count query on generate_series() and retrieve Null values as ‘0’
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment