Re: Stored Procedure / function and their result - Mailing list pgsql-general

Alain Roger wrote:
> I would like to know if there is a better way how to retrieve
> result from a stored procedure (function) than to use 'AS
> res(col1 varchar, col2 timestamp,..)'
>
> for example, here is a stored procedure :
> CREATE OR REPLACE FUNCTION SP_A_003(username VARCHAR)
>   RETURNS SETOF RECORD AS
[...]

Yes, there are two ways to avoid this.

1.) define a composite type:

CREATE TYPE sp_a_result (
    name varchar,
    firstname varchar,
    userlogin varchar,
    statustype varchar
);

CREATE OR REPLACE FUNCTION SP_A_003 (username VARCHAR)
  RETURNS SETOF sp_a_result AS ...

2.) Use output parameters (for Versions >= 8.1):

CREATE OR REPLACE FUNCTION SP_A_003 (
  username IN VARCHAR,
  name OUT varchar,
  firstname OUT varchar,
  userlogin OUT varchar,
  statustype OUT varchar
) RETURNS SETOF record AS ...

You can find a more verbose description in
http://www.postgresql.org/docs/current/static/xfunc-sql.html

Yours,
Laurenz Albe