CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1information-schema
Stefan Steiger
Stefan Steiger

Reputation: 82186

PostgreSQL: How to differentiate stored procedures and table-valued functions?

Question:

In Microsoft SQL Server, there are stored procedures, and there are table-valued functions.

The difference is, from a stored procedure, I cannot do further selects, while from a table-valued function, I can.

e.g.

SELECT * FROM sp_whatever WHERE xxx is illegal
while
SELECT * FROM TVF_whatever WHERE xxx is perfectly legal

Now my question:

In PostgreSQL, when I look at information_schema.routines, how can I differentiate table valued functions, and procedures ?

Is there a difference at all ?

And in general, how can I differentiate functions and procedures in PostgreSQL?

I mean theoretically, on SQL-server, one can differentiate them like this:
Table valued function: information_schema.data_type = 'table'
stored procedures: information_schema.data_type IS NULL
functions: information_schema.data_type != 'table' AND information_schema.data_type IS NOT NULL

How is this done in Postgres?

Theoretically, a stored procedure has return type void, but since a stored procedure can also return a table, there is no way to differentiate between tvf and stored precedure - assuming there is a difference.

So my question could also be formulated:
In PostGreSQL, how do I create a table-valued function, and how do I create a stored procedure (1 example each).

I'm interested in the difference in the return type between the two, if there is any.

Upvotes: 4

Views: 2209

Answers (1)

Frank Heikens
Frank Heikens

Reputation: 127086

PostgreSQL doesn't have real stored procedures, just user defined functions:

CREATE FUNCTION foo() RETURNS TABLE(bar INT, baz TEXT) ...

CREATE FUNCTION bar() RETURNS BOOLEAN ...

Check for datatype "record":

SELECT * FROM information_schema.routines WHERE data_type = 'record';

Upvotes: 3

Related Questions