PostgreSQL – How to Get the Function Name of a regprocedure

functionsparameterpostgresql

I'm trying to write a function to get the name of the function associated with a given regprocedure variable, as follows:

CREATE OR REPLACE FUNCTION get_funcname(_fn regprocedure)
  RETURNS text AS
$func$
  SELECT proname::text
  FROM pg_catalog.pg_proc AS p
  JOIN pg_catalog.pg_namespace AS ns
    ON p.pronamespace = ns.oid
  WHERE p.oid = _fn;
$func$  LANGUAGE sql;

I expect to get my_func in return for

SELECT get_funcname('my_func(text, variadic text[])');

However, the above is giving me the error:

ERROR:  syntax error at or near "variadic"
LINE 1: SELECT get_funcname('my_func(text, variadic text[])');
                                ^
CONTEXT:  invalid type name "variadic text[]"

Can anyone help explain how to fix the function or syntax?

Best Answer

You have

ERROR:  syntax error at or near "variadic"

From the docs,

VARIADIC parameters are input parameters, but are treated specially as described next.

That means you don't do anything special for VARIADIC parameters beyond what you would otherwise specify for input params. In your case, just drop that token,

SELECT get_funcname('my_func(text, text[])');

Can anyone help explain how to fix the function or syntax?

Sure.

WHERE p.oid = regexp_replace(_fn, ' VARIADIC ', ' ', 'i');

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;

Best Answer

Related Solutions

PL/SQL – How to Correctly Return Values from a Function

PostgreSQL – Is There a Type-Safe first() Aggregate Function?

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`