CODEWITHSUNDEEP
sqldatabasepostgresql
cassava
cassava

Reputation: 608

Where can functions appear in PostgreSQL?

Given a function xyz() in PostgreSQL, where and how can I actually use it?

Consider the function current_database() for example. I can perform the following queries then:

SELECT current_database();
SELECT * FROM current_database();

And in this case both result in the output:

 current_database
------------------
 cassava
(1 row)

Are there any other places I can use this function?

In particular: how could I write the following, so that it works (because as it stands, it does not).

GRANT ALL ON DATABASE current_database() TO GROUP wheel WITH GRANT OPTION;

Upvotes: 1

Views: 98

Answers (2)

cassava
cassava

Reputation: 608

Richard Huxton is right: wherever you can use an expression in an SQL statement, you should be able to use a function.

PostgreSQL has the DO extension however, which lets you run a procedural language without having to create a function. This makes building dynamic queries easy.

For the particular situation (omitting WITH GRANT OPTION):

DO $$
    BEGIN
    EXECUTE 'GRANT ALL ON DATABASE ' || current_database() || ' TO GROUP wheel';
    END;
$$;

This effectively lets you use a function anywhere.

Upvotes: 0

Richard Huxton
Richard Huxton

Reputation: 22893

You can't do the GRANT ALL example in pure SQL because the syntax requires an identifier - not an expression returning an identifier.

Anywhere you can use an expression in an SQL statement you should be able to use a function.

Beyond that you will need to use plpgsql (or one of the other procedural languages) to dynamically build a query-string.

Upvotes: 1

Related Questions