Select from a table variable

by

There are no “table variables” in plpgsql. That’s something you would find in SQL Server.

Use a temporary table instead:

BEGIN

CREATE TEMP TABLE table_holder AS
SELECT * FROM table_holder
WHERE <some condition>
ORDER BY <some expression>
;

...

END

A temporary table exists for the lifetime of a session. To drop it at the end of the function (or an enclosing transaction) automatically, use ON COMMIT DROP in the creating statement.

CREATE TEMP TABLE table_holder ON COMMIT DROP AS
SELECT ...

The temporary table is visible in the same session, but not outside.

One alternative would be to use cursors in PL/pgSQL.

More alternatives:

More Related Contents:

  1. Return SETOF rows from PostgreSQL function
  2. What are ‘$$’ used for in PL/pgSQL
  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. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  7. How to set value of composite variable field using dynamic SQL
  8. How do I do large non-blocking updates in PostgreSQL?
  9. Permission denied for relation
  10. Execute a dynamic crosstab query
  11. Is there a way to disable function overloading in Postgres
  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. How to make a select with array contains value clause in psql
  18. Test for null in function with varying parameters
  19. Postgres FOR LOOP
  20. Dynamically generate columns for crosstab in PostgreSQL
  21. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  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. Hashing a String to a Numeric Value in PostgreSQL
  26. PostgreSQL – set a default cell value according to another cell value
  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