How to create a temporary function in PostgreSQL?

by

I needed to know how to do a many time use in a script I was writing. Turns out you can create a temporary function using the pg_temp schema. This is a schema that is created on demand for your connection and is where temporary tables are stored. When your connection is closed or expires this schema is dropped. Turns out if you create a function on this schema, the schema will be created automatically. Therefore,

create function pg_temp.testfunc() returns text as 
$$ select 'hello'::text $$ language sql;

will be a function that will stick around as long as your connection sticks around. No need to call a drop command.

More Related Contents:

  1. Select rows which are not present in other table
  2. Optimize GROUP BY query to retrieve latest row per user
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. Computed / calculated / virtual / derived columns in PostgreSQL
  5. Function vs. Stored Procedure in SQL Server
  6. Check if value exists in Postgres array
  7. Is there a shortcut for SELECT * FROM?
  8. sql statement error: “column .. does not exist”
  9. What is the difference between single quotes and double quotes in PostgreSQL?
  10. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  11. PostgreSQL: Which Datatype should be used for Currency?
  12. Call a set-returning function with an array argument multiple times
  13. Permanently Set Postgresql Schema Path
  14. Fastest check if row exists in PostgreSQL
  15. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  16. Slow simple update query on PostgreSQL database with 3 million rows
  17. How do I alter the position of a column in a PostgreSQL database table?
  18. Discard millisecond part from timestamp
  19. Return setof record (virtual table) from function
  20. Transposing an sql result so that one column goes onto multiple columns
  21. SQL column reference “id” is ambiguous
  22. IN Clause with NULL or IS NULL
  23. How to convert PostgreSQL 9.4’s jsonb type to float
  24. postgres – where in (list) – column does not exist
  25. incomplete information from query on pg_views [duplicate]
  26. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  27. Referencing current row in FILTER clause of window function
  28. Temporal database design, with a twist (live vs draft rows)
  29. Why do SQL Server Scalar-valued functions get slower?
  30. How to bulk insert only new rows in PostreSQL

Leave a Comment