Is there a way of doing:
RETURNS INTEGER, CHAR(5), VARCHAR(200),.....
IE return multiple values from a PL/PGSQL function?

(From what I can see so far, you only have 1 return field - although
that can be a rowtype or record)

I am converting Informix functions, and returning multiple columns was
allowed - I need to be able to do the same/similar in PostGre. What's
the best way?

(Typically the functions return an error code, error text (that are not
table based - ie are built in the function and not selected from a
table) and two or three columns from a table).




Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769

Steve Tucknott <steve (AT) retsol (DOT) co.uk> writes:
You have to return a rowtype value.

There's an example in the 8.0devel docs:
http://developer.postgresql.org/docs...QL-PORTING-EX3

The example is making use of an 8.0-only feature (explicit names for
parameters) but otherwise I believe it would work in 7.4. Not sure
about pre-7.4.

BTW, the correct name of our software is PostgreSQL or informally
Postgres. No one associated with the project has ever called it
"Postgre". Pronounce it "post-gress" or "post-gress-cue-ell".

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Steve Tucknott <steve (AT) retsol (DOT) co.uk> writes:
In the form I showed, yes.

Only if you're prepared to specify the actual record type in the calling
query. The point is that in

select * from myfunc(...);

the parser has to have some way of understanding what * expands to,
and it needs the info in advance of calling the function. So you
either need to return a named rowtype, or return record and specify
what you're expecting in the call. From memory it's something like

select * from myfunc(...) AS (f1 int, f2 text, ...);

but see the docs. In practice I think the named rowtype is easier in
99% of cases. The returns-record case is really meant for functions
that can actually return different rowtypes depending on the parameters
they are given, like dblink() does. If you're thinking of doing something
like that, you probably shouldn't be asking about it on the novice list ;-)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Tom,
Thanks again for the reply.

We're calling the 'function' from a 4gl program - I'm just trying the
'Record' type route to make sure that the 4gl understands the returned
type. If not, then named row types will have to be the option.

Cheers again.

On Mon, 2004-08-09 at 15:23, Tom Lane wrote:

Steve Tucknott <steve (AT) retsol (DOT) co.uk> writes:
In the form I showed, yes.

Only if you're prepared to specify the actual record type in the calling
query. The point is that in

select * from myfunc(...);

the parser has to have some way of understanding what * expands to,
and it needs the info in advance of calling the function. So you
either need to return a named rowtype, or return record and specify
what you're expecting in the call. From memory it's something like

select * from myfunc(...) AS (f1 int, f2 text, ...);

but see the docs. In practice I think the named rowtype is easier in
99% of cases. The returns-record case is really meant for functions
that can actually return different rowtypes depending on the parameters
they are given, like dblink() does. If you're thinking of doing something
like that, you probably shouldn't be asking about it on the novice list ;-)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match



Regards,

Steve Tucknott

ReTSol Ltd

DDI: 01903 828769