From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: list@racistnames.com
To: pgsql-sql@postgresql.org
Subject: Re: returning composite types.
Date: Sat, 29 Mar 2003 13:20:05 -0300
Message-ID: <200303291320.05734.franco@akyasociados.com.ar> (raw)
In-Reply-To: <3E84E983.10206@racistnames.com>
References: <200303282020.25997.franco@akyasociados.com.ar>
<3E84E983.10206@racistnames.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: franco@akyasociados.com.ar, list@racistnames.com
Subject: Re: returning composite types.
In-Reply-To: <200303291320.05734.franco@akyasociados.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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