Alternative to pg_get_functioncode?

[Phil Hibbs]

Is there a way of querying a function definition in Greenplum?

Phil.

[Heikki Linnakangas]

On 01/02/16 18:36, Phil Hibbs wrote:
> Is there a way of querying a function definition in Greenplum?

There's no equivalent of pg_get_functiondef() that would return the full
"CREATE FUNCTION" statement, unfortunately. You can use "pg_dump -s" and
find the statement from the dump. And "\df+" in psql will show the
function name, arguments, source, etc. information needed to reconstruct
it, but it won't give a straight CREATE FUNCTION statement.

pg_get_functiondef() was introduced in PostgreSQL 8.4, so as we merge
that into GPDB, we'll get it. But it'll be quite a while until we get there.

- Heikki

[Danilo Fortunato]

From a GUI tool, get the function definition using pgAdmin III or Aginity Workbench for Pivotal Greenplum, which are the two main GUI tools for working with Greenplum.

From the command line, you can use psql, but as already said it won't give the full CREATE FUNCTION statement:
\a
\o <function name>.sql
\df+ <schema name>.<function name>
\o

To get the full CREATE FUNCTION statement from the command line you can use pg_dump and pg_restore:

- create a dump file containing all the objects of the schema containing your function(s):
pg_dump -h <host name> -U <user name> -n <schema name> -Fc -s <database name> > dump.dmp

- search the function definition including its argument list within the dump file:
pg_restore -l dump.dmp | grep <function name>

- extract the function definition from the dump file:
pg_restore -P '<function name>(arguments list)' dump.dmp > <function name>.sql

Regards,
Danilo

---

Da: Phil Hibbs (sna...@gmail.com)
Inviato: Lunedì 1 Febbraio 2016 17:36
A: Greenplum Users (gpdb-...@greenplum.org)
Oggetto: [gpdb-users] Alternative to pg_get_functioncode?

 

Is there a way of querying a function definition in Greenplum?

Phil.

--
You received this message because you are subscribed to the Google Groups "Greenplum Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to gpdb-users+...@greenplum.org.
To post to this group, send email to gpdb-...@greenplum.org.
Visit this group at https://groups.google.com/a/greenplum.org/group/gpdb-users/.
For more options, visit https://groups.google.com/a/greenplum.org/d/optout.