pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

PLSQL function calling another function
2+ messages / 2 participants
[nested] [flat]

* PLSQL function calling another function
@ 2008-10-09 14:26  cyw@dls.net
  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 16:08  Tom Lane <tgl@sss.pgh.pa.us>
  parent: cyw@dls.net
  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