pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPLSQL function calling another function
2+ messages / 2 participants
[nested] [flat]
* PLSQL function calling another function
@ 2008-10-09 14:26 cyw@dls.net
2008-10-09 16:08 ` Re: PLSQL function calling another function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: cyw@dls.net @ 2008-10-09 14:26 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hi All,
I am trying to figure out how to call one PLSQL function from another, specifically how to access the return values from the callee.
I made two test functions, A and B. A calls B.
B returns two values:
OUT tid integer
OUT msg character varying
In caller function A, I do the following:
SELECT B(1) INTO rec; -- rec declared as a RECORD
RAISE DEBUG 'XXX % [%]', rec.tid, rec.msg;
The runtime error I got was:
ERROR: record "rec" has no field "tid"
SQL state: 42703
The full function code is below.
Thanks,
CYW
--------------------------------
FUNCTION B(IN id integer, OUT tid integer, OUT msg character varying) RETURNS record AS BODY$
DECLARE
BEGIN
msg := 'MSG';
tid := 100;
RETURN;
END;
FUNCTION A(IN x integer, IN "text" character varying, OUT whatever character varying) RETURNS character varying AS $BODY$
DECLARE
rec RECORD;
tid int4;
msg varchar;
BEGIN
SELECT B(1) INTO rec;
RAISE DEBUG 'XXX % [%]', rec.tid, rec.msg;
RETURN;
END;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PLSQL function calling another function
2008-10-09 14:26 PLSQL function calling another function cyw@dls.net
@ 2008-10-09 16:08 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2008-10-09 16:08 UTC (permalink / raw)
To: cyw@dls.net; +Cc: pgsql-general@postgresql.org
<cyw@dls.net> writes:
> I am trying to figure out how to call one PLSQL function from another, specifically how to access the return values from the callee.
> I made two test functions, A and B. A calls B.
> B returns two values:
> OUT tid integer
> OUT msg character varying
> In caller function A, I do the following:
> SELECT B(1) INTO rec; -- rec declared as a RECORD
This produces a record containing a single composite column (named "b"),
as you would see if you did the same SELECT by hand:
regression=# select b(1);
b
-----------
(100,MSG)
(1 row)
You will get the results you expect if you do something like
select * into rec from b(1);
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-10-09 16:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-10-09 14:26 PLSQL function calling another function cyw@dls.net
2008-10-09 16:08 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox