PostgreSQL function-OID: 'my OID' where 'my' is the currently executing user-defined-function

Question

This query returns the OID of the function whose name and signature is supplied:

  select 'myfunc(signature)'::regprocedure::oid;

But is there something in PostgreSQL plpgsql like a myNameAndSignature() function so we could use dynamic sql to build a statement that gets the OID of the function and then creates a temporary table with the OID appended to the name of the temp table?
The statement to execute dynamically is:

create temp table TT17015

I'm new to PostgreSQL, and maybe there's a better way to handle naming of temporary tables so the functions that use temp tables, and call each other, don't get the error that a particular temp table it is trying to delete is in use elsewhere?

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2013-01-27T19:30:57

Using the OID of a function does not necessarily prevent a naming conflict. The same function could be run multiple times in the same session.

If you are in need of a unique name, use a SEQUENCE. Run once in your database:

CREATE SEQUENCE tt_seq;

Then, in your plpgsql function or DO statement:

DO
$$
DECLARE
    _tbl text := 'tt' || nextval('tt_seq');
BEGIN
    EXECUTE 'CREATE TEMP TABLE ' || _tbl || '(id int)';
END
$$

Drawback is that you have to use dynamic SQL for dynamic identifiers. Plain SQL commands do not accept parameters for identifiers.

PostgreSQL function-OID: 'my OID' where 'my' is the currently executing user-defined-function

1 Answers