pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreturning composite types.
7+ messages / 3 participants
[nested] [flat]
* returning composite types.
@ 2003-03-28 23:20 Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 00:32 ` Re: returning composite types. Jordan S. Jones <list@racistnames.com>
0 siblings, 1 reply; 7+ messages in thread
From: Franco Bruno Borghesi @ 2003-03-28 23:20 UTC (permalink / raw)
To: pgsql-sql
Hi guys.
I'm working with functions in my database, using plpgsql, but I reached a
point where I realize I'm missing a concept: how do I return composite types
from a function? I'll give you an example:
CREATE TYPE mytype AS(
val1 INTEGER,
val2 INTEGER,
val3 INTEGER,
val4 INTEGER
);
If I want my function to return a "mytype" type, should I declare it as:
CREATE FUNCTION myFunction() RETURNS mytype AS ...
or maybe
CREATE FUNCTION myFunction() RETURNS SETOF mytype AS ...
and in any case, inside the function, how should I declare the variable
holding the return value?:
DECLARE
result mytype;
BEGIN
...
RETURN result;
END;
or maybe
DECLARE
result mytype%ROWTYPE;
BEGIN
...
RETURN result;
END;
I've read the documentation and the examples in it, but I still don't
understand what the right way is. If you could give an example of a function
filling "mytipe" and returning it, it would really help me.
Thanks in advance.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: returning composite types.
2003-03-28 23:20 returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
@ 2003-03-29 00:32 ` Jordan S. Jones <list@racistnames.com>
2003-03-29 16:20 ` Re: returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 1 reply; 7+ messages in thread
From: Jordan S. Jones @ 2003-03-29 00:32 UTC (permalink / raw)
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; pgsql-sql
Here is how I have been doing mine:
CREATE FUNCTION "myFunction" () RETURNS SETOF mytype
AS
'
DECLARE
r mytype%ROWTYPE;
BEGIN
FOR r IN [SELECT STATEMENT]
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
'
LANGUAGE 'plpgsql';
Hope this Helps..
Jordan S. Jones
Franco Bruno Borghesi wrote:
>Hi guys.
>
>I'm working with functions in my database, using plpgsql, but I reached a
>point where I realize I'm missing a concept: how do I return composite types
>from a function? I'll give you an example:
>
>CREATE TYPE mytype AS(
> val1 INTEGER,
> val2 INTEGER,
> val3 INTEGER,
> val4 INTEGER
>);
>
>If I want my function to return a "mytype" type, should I declare it as:
>CREATE FUNCTION myFunction() RETURNS mytype AS ...
>or maybe
>CREATE FUNCTION myFunction() RETURNS SETOF mytype AS ...
>
>and in any case, inside the function, how should I declare the variable
>holding the return value?:
>
>DECLARE
> result mytype;
>BEGIN
>...
> RETURN result;
>END;
>
>or maybe
>
>DECLARE
> result mytype%ROWTYPE;
>BEGIN
>...
> RETURN result;
>END;
>
>I've read the documentation and the examples in it, but I still don't
>understand what the right way is. If you could give an example of a function
>filling "mytipe" and returning it, it would really help me.
>
>Thanks in advance.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: returning composite types.
2003-03-28 23:20 returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 00:32 ` Re: returning composite types. Jordan S. Jones <list@racistnames.com>
@ 2003-03-29 16:20 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 16:49 ` Re: returning composite types. Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 7+ messages in thread
From: Franco Bruno Borghesi @ 2003-03-29 16:20 UTC (permalink / raw)
To: list@racistnames.com; pgsql-sql
ok, soy you're telling me that the only way to return a composite type is
using a set of them, even if I know my function will allways return 1 record.
And tell me: can I assign values to this record without using the "for" as you
did? maybe a SELECT INTO, or something.
I'll tell you what I'm trying to do, maybe that will help: I have 4 functions,
that do specific tasks, and they are called in a chained mode. I want the
first function in the chain to call the other functions, and return all the
results in a composite type, like this:
CREATE TYPE mytype AS(
val1 INTEGER,
val2 INTEGER,
val3 INTEGER,
val4 INTEGER
);
CREATE FUNCTION f1() RETURNS SETOF mytype AS '
DECLARE
result mytype%ROWTYPE;
BEGIN
result.val1=/*something*/;
SELECT * INTO result FROM f2(result);
RETURN NEXT result;
RETURN;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION f2(mytype) RETURNS SETOF mytype AS '
DECLARE
result ALIAS FOR $1;
BEGIN
result.val2=2;
IF (result.val1>50) THEN
SELECT * INTO result FROM f3(result);
ELSE
SELECT * INTO result FROM f4(result);
END IF;
RETURN NEXT result;
RETURN;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION f3(mytype) RETURNS SETOF mytype AS '
DECLARE
result ALIAS FOR $1;
BEGIN
/*do something with result*/
RETURN NEXT result;
RETURN;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION f4(mytype) RETURNS SETOF mytype AS '
DECLARE
result ALIAS FOR $1;
BEGIN
/*do something with result*/
RETURN NEXT result;
RETURN;
END;
' LANGUAGE 'plpgsql';
In a programming language I would pass mytype by reference, and I was trying
to something like that here in plpgsql.
On Friday 28 March 2003 21:32, Jordan S. Jones wrote:
> Here is how I have been doing mine:
>
> CREATE FUNCTION "myFunction" () RETURNS SETOF mytype
> AS
> '
> DECLARE
> r mytype%ROWTYPE;
> BEGIN
> FOR r IN [SELECT STATEMENT]
> LOOP
> RETURN NEXT r;
> END LOOP;
> RETURN;
> END;
> '
> LANGUAGE 'plpgsql';
>
> Hope this Helps..
>
> Jordan S. Jones
>
> Franco Bruno Borghesi wrote:
> >Hi guys.
> >
> >I'm working with functions in my database, using plpgsql, but I reached a
> >point where I realize I'm missing a concept: how do I return composite
> > types from a function? I'll give you an example:
> >
> >CREATE TYPE mytype AS(
> > val1 INTEGER,
> > val2 INTEGER,
> > val3 INTEGER,
> > val4 INTEGER
> >);
> >
> >If I want my function to return a "mytype" type, should I declare it as:
> >CREATE FUNCTION myFunction() RETURNS mytype AS ...
> >or maybe
> >CREATE FUNCTION myFunction() RETURNS SETOF mytype AS ...
> >
> >and in any case, inside the function, how should I declare the variable
> >holding the return value?:
> >
> >DECLARE
> > result mytype;
> >BEGIN
> >...
> > RETURN result;
> >END;
> >
> >or maybe
> >
> >DECLARE
> > result mytype%ROWTYPE;
> >BEGIN
> >...
> > RETURN result;
> >END;
> >
> >I've read the documentation and the examples in it, but I still don't
> >understand what the right way is. If you could give an example of a
> > function filling "mytipe" and returning it, it would really help me.
> >
> >Thanks in advance.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: returning composite types.
2003-03-28 23:20 returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 00:32 ` Re: returning composite types. Jordan S. Jones <list@racistnames.com>
2003-03-29 16:20 ` Re: returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
@ 2003-03-29 16:49 ` Joe Conway <mail@joeconway.com>
2003-03-29 17:35 ` Re: returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 1 reply; 7+ messages in thread
From: Joe Conway @ 2003-03-29 16:49 UTC (permalink / raw)
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; +Cc: list@racistnames.com; pgsql-sql
Franco Bruno Borghesi wrote:
> ok, soy you're telling me that the only way to return a composite type is
> using a set of them, even if I know my function will allways return 1 record.
Try this:
create type foo as (f1 int, f2 text);
create or replace function retfoo(int, text) returns foo as '
declare
result foo%ROWTYPE;
begin
select into result $1, $2;
return result;
end;
' language 'plpgsql';
regression=# select * from retfoo(2,'b');
f1 | f2
----+----
2 | b
(1 row)
Joe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: returning composite types.
2003-03-28 23:20 returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 00:32 ` Re: returning composite types. Jordan S. Jones <list@racistnames.com>
2003-03-29 16:20 ` Re: returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 16:49 ` Re: returning composite types. Joe Conway <mail@joeconway.com>
@ 2003-03-29 17:35 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 18:25 ` Re: returning composite types. Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 7+ messages in thread
From: Franco Bruno Borghesi @ 2003-03-29 17:35 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: list@racistnames.com; pgsql-sql
yes, i was trying to do something like that, but it breaks always in the same
place, first I thought that it was because of the way I was assigning values
to the fields of my row, but now I'm beginning to think that the reason is
the way I pass the row to f2.
Here is the error:
franco=# SELECT f1();
WARNING: Error occurred while executing PL/pgSQL function f1
WARNING: line 5 at select into variables
ERROR: Attribute "result" not found
CREATE TYPE mytype AS (val1 INTEGER, val2 INTEGER, val3 INTEGER);
CREATE OR REPLACE FUNCTION f1() RETURNS mytype AS '
DECLARE
result mytype%ROWTYPE;
BEGIN
result.val1:=1;
SELECT val2, val3 INTO result.val2, result.val3 FROM f2(result);
RETURN result;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION f2(mytype) RETURNS mytype AS '
DECLARE
arg ALIAS FOR $1;
result mytype%ROWTYPE;
BEGIN
arg.val2:=2;
arg.val3:=3;
SELECT arg.val1, arg.val2, arg.val3 INTO result.val1, result.val2,
result.val3;
RETURN result;
END;
' LANGUAGE 'plpgsql';
what do you guys think?
On Saturday 29 March 2003 13:49, Joe Conway wrote:
> Franco Bruno Borghesi wrote:
> > ok, soy you're telling me that the only way to return a composite type is
> > using a set of them, even if I know my function will allways return 1
> > record.
>
> Try this:
>
> create type foo as (f1 int, f2 text);
> create or replace function retfoo(int, text) returns foo as '
> declare
> result foo%ROWTYPE;
> begin
> select into result $1, $2;
> return result;
> end;
> ' language 'plpgsql';
>
> regression=# select * from retfoo(2,'b');
> f1 | f2
> ----+----
> 2 | b
> (1 row)
>
> Joe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: returning composite types.
2003-03-28 23:20 returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 00:32 ` Re: returning composite types. Jordan S. Jones <list@racistnames.com>
2003-03-29 16:20 ` Re: returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 16:49 ` Re: returning composite types. Joe Conway <mail@joeconway.com>
2003-03-29 17:35 ` Re: returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
@ 2003-03-29 18:25 ` Joe Conway <mail@joeconway.com>
2003-03-29 18:56 ` Re: returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 1 reply; 7+ messages in thread
From: Joe Conway @ 2003-03-29 18:25 UTC (permalink / raw)
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>; +Cc: list@racistnames.com; pgsql-sql
Franco Bruno Borghesi wrote:
> yes, i was trying to do something like that, but it breaks always in the same
> place, first I thought that it was because of the way I was assigning values
> to the fields of my row, but now I'm beginning to think that the reason is
> the way I pass the row to f2.
>
> Here is the error:
> franco=# SELECT f1();
> WARNING: Error occurred while executing PL/pgSQL function f1
> WARNING: line 5 at select into variables
> ERROR: Attribute "result" not found
>
> CREATE TYPE mytype AS (val1 INTEGER, val2 INTEGER, val3 INTEGER);
>
> CREATE OR REPLACE FUNCTION f1() RETURNS mytype AS '
> DECLARE
> result mytype%ROWTYPE;
> BEGIN
> result.val1:=1;
> SELECT val2, val3 INTO result.val2, result.val3 FROM f2(result);
It looks like plpgsql doesn't support composite type variables as
arguments to functions that are called from within a function. The error
is saying it cannot find an attribute named result -- that's because
there is no *attribute* called result, there is a rowtype variable.
I'm not sure right off what is involved in fixing this, but you can
always pass the individual attributes to f2:
CREATE TYPE mytype AS (val1 INTEGER, val2 INTEGER, val3 INTEGER);
CREATE OR REPLACE FUNCTION f2(int,int,int) RETURNS mytype AS '
DECLARE
arg1 ALIAS FOR $1;
arg2 ALIAS FOR $2;
arg3 ALIAS FOR $3;
newval2 int;
newval3 int;
result mytype%ROWTYPE;
BEGIN
newval2 := coalesce(arg2,2) * arg1;
newval3 := coalesce(arg3,3) * arg1;
SELECT INTO result.val1, result.val2, result.val3
arg1, newval2, newval3;
RETURN result;
END;
' LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION f1(int) RETURNS mytype AS '
DECLARE
result mytype%ROWTYPE;
BEGIN
result.val1:=$1;
SELECT INTO result.val2, result.val3 val2, val3
FROM f2(result.val1, result.val2, result.val3);
RETURN result;
END;
' LANGUAGE 'plpgsql';
regression=# select * from f1(2);
val1 | val2 | val3
------+------+------
2 | 4 | 6
(1 row)
HTH,
Joe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: returning composite types.
2003-03-28 23:20 returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 00:32 ` Re: returning composite types. Jordan S. Jones <list@racistnames.com>
2003-03-29 16:20 ` Re: returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 16:49 ` Re: returning composite types. Joe Conway <mail@joeconway.com>
2003-03-29 17:35 ` Re: returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 18:25 ` Re: returning composite types. Joe Conway <mail@joeconway.com>
@ 2003-03-29 18:56 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 0 replies; 7+ messages in thread
From: Franco Bruno Borghesi @ 2003-03-29 18:56 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: list@racistnames.com; pgsql-sql
so what you're saying is that it's not a syntactical error, it's just an
unsupported feature.
Ok then, I will just call my functions sending the values from my composite
type individually.
thanks guys!
On Saturday 29 March 2003 15:25, Joe Conway wrote:
> Franco Bruno Borghesi wrote:
> > yes, i was trying to do something like that, but it breaks always in the
> > same place, first I thought that it was because of the way I was
> > assigning values to the fields of my row, but now I'm beginning to think
> > that the reason is the way I pass the row to f2.
> >
> > Here is the error:
> > franco=# SELECT f1();
> > WARNING: Error occurred while executing PL/pgSQL function f1
> > WARNING: line 5 at select into variables
> > ERROR: Attribute "result" not found
> >
> > CREATE TYPE mytype AS (val1 INTEGER, val2 INTEGER, val3 INTEGER);
> >
> > CREATE OR REPLACE FUNCTION f1() RETURNS mytype AS '
> > DECLARE
> > result mytype%ROWTYPE;
> > BEGIN
> > result.val1:=1;
> > SELECT val2, val3 INTO result.val2, result.val3 FROM f2(result);
>
> It looks like plpgsql doesn't support composite type variables as
> arguments to functions that are called from within a function. The error
> is saying it cannot find an attribute named result -- that's because
> there is no *attribute* called result, there is a rowtype variable.
>
> I'm not sure right off what is involved in fixing this, but you can
> always pass the individual attributes to f2:
>
> CREATE TYPE mytype AS (val1 INTEGER, val2 INTEGER, val3 INTEGER);
>
> CREATE OR REPLACE FUNCTION f2(int,int,int) RETURNS mytype AS '
> DECLARE
> arg1 ALIAS FOR $1;
> arg2 ALIAS FOR $2;
> arg3 ALIAS FOR $3;
> newval2 int;
> newval3 int;
> result mytype%ROWTYPE;
> BEGIN
> newval2 := coalesce(arg2,2) * arg1;
> newval3 := coalesce(arg3,3) * arg1;
> SELECT INTO result.val1, result.val2, result.val3
> arg1, newval2, newval3;
> RETURN result;
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE OR REPLACE FUNCTION f1(int) RETURNS mytype AS '
> DECLARE
> result mytype%ROWTYPE;
> BEGIN
> result.val1:=$1;
> SELECT INTO result.val2, result.val3 val2, val3
> FROM f2(result.val1, result.val2, result.val3);
> RETURN result;
> END;
> ' LANGUAGE 'plpgsql';
>
> regression=# select * from f1(2);
> val1 | val2 | val3
> ------+------+------
> 2 | 4 | 6
> (1 row)
>
> HTH,
>
> Joe
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-03-29 18:56 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-28 23:20 returning composite types. Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 00:32 ` Jordan S. Jones <list@racistnames.com>
2003-03-29 16:20 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 16:49 ` Joe Conway <mail@joeconway.com>
2003-03-29 17:35 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2003-03-29 18:25 ` Joe Conway <mail@joeconway.com>
2003-03-29 18:56 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
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