pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction problem
13+ messages / 4 participants
[nested] [flat]
* function problem
@ 2003-10-21 12:25 geraldo <geraldo@umc.br>
2003-10-21 19:08 ` Re: function problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: geraldo @ 2003-10-21 12:25 UTC (permalink / raw)
To: pgsql-sql
Can anybody tell me why the following code when activated
by a select only affects the first line of the table???
create or replace function increase(integer)
returns void as 'update tab set price=price*(1+$1/100.0)'
language sql;
Thanks.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: function problem
2003-10-21 12:25 function problem geraldo <geraldo@umc.br>
@ 2003-10-21 19:08 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2003-10-21 19:08 UTC (permalink / raw)
To: geraldo <geraldo@umc.br>; +Cc: pgsql-sql
"geraldo" <geraldo@umc.br> writes:
> Can anybody tell me why the following code when activated
> by a select only affects the first line of the table???
> create or replace function increase(integer)
> returns void as 'update tab set price=price*(1+$1/100.0)'
> language sql;
Works fine here.
regression=# create table tab (price numeric);
CREATE TABLE
regression=# insert into tab values(1);
INSERT 154584 1
regression=# insert into tab values(10);
INSERT 154585 1
regression=# insert into tab values(100);
INSERT 154586 1
regression=# select * from tab;
price
-------
1
10
100
(3 rows)
regression=# create or replace function increase(integer)
regression-# returns void as 'update tab set price=price*(1+$1/100.0)'
regression-# language sql;
CREATE FUNCTION
regression=# select increase(42);
increase
----------
(1 row)
regression=# select * from tab;
price
--------------------------
1.42000000000000000000
14.20000000000000000000
142.00000000000000000000
(3 rows)
regression=#
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* FUNCTION problem
@ 2009-04-01 23:31 Peter Willis <peterw@borstad.com>
2009-04-01 23:55 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 13+ messages in thread
From: Peter Willis @ 2009-04-01 23:31 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am having a problem with a FUNCTION.
The function creates just fine with no errors.
However, when I call the function postgres produces an error.
Perhaps someone can enlighten me.
--I can reproduce the error by making a test function
--that is much easier to follow that the original:
CREATE OR REPLACE FUNCTION test_function(integer)
RETURNS SETOF RECORD AS
$BODY$
DECLARE croid integer;
BEGIN
--PERFORM A SMALL CALCULATION
--DOESNT SEEM TO MATTER WHAT IT IS
SELECT INTO croid 2;
--A SELECT STATEMENT OUTPUTS RECORDS (one in this case)
SELECT croid,$1;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
--The call looks like the following:
SELECT test_function(1);
--The resulting error reads as follows:
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function "test_function" line 5 at SQL statement
********** Error **********
ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Context: PL/pgSQL function "test_function" line 5 at SQL statement
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FUNCTION problem
2009-04-01 23:31 FUNCTION problem Peter Willis <peterw@borstad.com>
@ 2009-04-01 23:55 ` Adrian Klaver <aklaver@comcast.net>
2009-04-02 16:09 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
0 siblings, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2009-04-01 23:55 UTC (permalink / raw)
To: pgsql-sql; peterw@borstad.com
On Wednesday 01 April 2009 4:31:20 pm Peter Willis wrote:
> Hello,
>
> I am having a problem with a FUNCTION.
> The function creates just fine with no errors.
>
> However, when I call the function postgres produces an error.
>
> Perhaps someone can enlighten me.
>
>
> --I can reproduce the error by making a test function
> --that is much easier to follow that the original:
>
> CREATE OR REPLACE FUNCTION test_function(integer)
> RETURNS SETOF RECORD AS
> $BODY$
> DECLARE croid integer;
> BEGIN
>
> --PERFORM A SMALL CALCULATION
> --DOESNT SEEM TO MATTER WHAT IT IS
>
> SELECT INTO croid 2;
>
> --A SELECT STATEMENT OUTPUTS RECORDS (one in this case)
> SELECT croid,$1;
> END;
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
>
>
>
>
> --The call looks like the following:
>
> SELECT test_function(1);
>
>
>
>
>
> --The resulting error reads as follows:
>
> ERROR: query has no destination for result data
> HINT: If you want to discard the results of a SELECT, use PERFORM instead.
> CONTEXT: PL/pgSQL function "test_function" line 5 at SQL statement
>
> ********** Error **********
>
> ERROR: query has no destination for result data
> SQL state: 42601
> Hint: If you want to discard the results of a SELECT, use PERFORM instead.
> Context: PL/pgSQL function "test_function" line 5 at SQL statement
You have declared function to RETURN SETOF. In order for that to work you need
to do RETURN NEXT. See below for difference between RETURN and RETURN NEXT:
http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUC...
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FUNCTION problem
2009-04-01 23:31 FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-01 23:55 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
@ 2009-04-02 16:09 ` Peter Willis <peterw@borstad.com>
2009-04-02 22:20 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 13+ messages in thread
From: Peter Willis @ 2009-04-02 16:09 UTC (permalink / raw)
To: Adrian Klaver <aklaver@comcast.net>; +Cc: pgsql-sql
Adrian Klaver wrote:
> On Wednesday 01 April 2009 4:31:20 pm Peter Willis wrote:
>> Hello,
>>
>> I am having a problem with a FUNCTION.
>> The function creates just fine with no errors.
>>
>> However, when I call the function postgres produces an error.
>>
>> Perhaps someone can enlighten me.
>>
>>
>> --I can reproduce the error by making a test function
>> --that is much easier to follow that the original:
>>
>> CREATE OR REPLACE FUNCTION test_function(integer)
>> RETURNS SETOF RECORD AS
>> $BODY$
>> DECLARE croid integer;
>> BEGIN
>>
>> --PERFORM A SMALL CALCULATION
>> --DOESNT SEEM TO MATTER WHAT IT IS
>>
>> SELECT INTO croid 2;
>>
>> --A SELECT STATEMENT OUTPUTS RECORDS (one in this case)
>> SELECT croid,$1;
>> END;
>>
>> $BODY$
>> LANGUAGE 'plpgsql' VOLATILE
>>
>>
>>
>>
>> --The call looks like the following:
>>
>> SELECT test_function(1);
>>
>>
>>
>>
>>
>> --The resulting error reads as follows:
>>
>> ERROR: query has no destination for result data
>> HINT: If you want to discard the results of a SELECT, use PERFORM instead.
>> CONTEXT: PL/pgSQL function "test_function" line 5 at SQL statement
>>
>> ********** Error **********
>>
>> ERROR: query has no destination for result data
>> SQL state: 42601
>> Hint: If you want to discard the results of a SELECT, use PERFORM instead.
>> Context: PL/pgSQL function "test_function" line 5 at SQL statement
>
> You have declared function to RETURN SETOF. In order for that to work you need
> to do RETURN NEXT. See below for difference between RETURN and RETURN NEXT:
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUC...
>
>
Thank you for the pointer.
I tried using FOR/RETURN NEXT as suggested but now get a
different error:
CREATE OR REPLACE FUNCTION test_function(integer)
RETURNS SETOF record AS
$BODY$
DECLARE croid integer;
DECLARE R RECORD;
BEGIN
SELECT INTO croid 2;
FOR R IN SELECT croid,$1 LOOP
RETURN NEXT R;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
There is now an error :
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "test_function" line 7 at RETURN NEXT
********** Error **********
ERROR: set-valued function called in context that cannot accept a set
SQL state: 0A000
Context: PL/pgSQL function "test_function" line 7 at RETURN NEXT
PostgreSQL doesn't seem to see 'R' as being a
SET OF RECORD....
Peter
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FUNCTION problem
2009-04-01 23:31 FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-01 23:55 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 16:09 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
@ 2009-04-02 22:20 ` Adrian Klaver <aklaver@comcast.net>
2009-04-02 23:22 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
0 siblings, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2009-04-02 22:20 UTC (permalink / raw)
To: peterw@borstad.com; +Cc: pgsql-sql
----- "Peter Willis" <peterw@borstad.com> wrote:
> Adrian Klaver wrote:
> > On Wednesday 01 April 2009 4:31:20 pm Peter Willis wrote:
> >> Hello,
> >>
> >> I am having a problem with a FUNCTION.
> >> The function creates just fine with no errors.
> >>
> >> However, when I call the function postgres produces an error.
> >>
> >> Perhaps someone can enlighten me.
> >>
> >>
> >> --I can reproduce the error by making a test function
> >> --that is much easier to follow that the original:
> >>
> >> CREATE OR REPLACE FUNCTION test_function(integer)
> >> RETURNS SETOF RECORD AS
> >> $BODY$
> >> DECLARE croid integer;
> >> BEGIN
> >>
> >> --PERFORM A SMALL CALCULATION
> >> --DOESNT SEEM TO MATTER WHAT IT IS
> >>
> >> SELECT INTO croid 2;
> >>
> >> --A SELECT STATEMENT OUTPUTS RECORDS (one in this case)
> >> SELECT croid,$1;
> >> END;
> >>
> >> $BODY$
> >> LANGUAGE 'plpgsql' VOLATILE
> >>
> >>
> >>
> >>
> >> --The call looks like the following:
> >>
> >> SELECT test_function(1);
> >>
> >>
> >>
> >>
> >>
> >> --The resulting error reads as follows:
> >>
> >> ERROR: query has no destination for result data
> >> HINT: If you want to discard the results of a SELECT, use PERFORM
> instead.
> >> CONTEXT: PL/pgSQL function "test_function" line 5 at SQL
> statement
> >>
> >> ********** Error **********
> >>
> >> ERROR: query has no destination for result data
> >> SQL state: 42601
> >> Hint: If you want to discard the results of a SELECT, use PERFORM
> instead.
> >> Context: PL/pgSQL function "test_function" line 5 at SQL statement
> >
> > You have declared function to RETURN SETOF. In order for that to
> work you need
> > to do RETURN NEXT. See below for difference between RETURN and
> RETURN NEXT:
> >
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUC...
> >
> >
>
>
> Thank you for the pointer.
>
> I tried using FOR/RETURN NEXT as suggested but now get a
> different error:
>
>
> CREATE OR REPLACE FUNCTION test_function(integer)
> RETURNS SETOF record AS
> $BODY$
> DECLARE croid integer;
> DECLARE R RECORD;
> BEGIN
> SELECT INTO croid 2;
>
> FOR R IN SELECT croid,$1 LOOP
> RETURN NEXT R;
> END LOOP;
> RETURN;
> END;
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
>
>
> There is now an error :
>
> ERROR: set-valued function called in context that cannot accept a
> set
> CONTEXT: PL/pgSQL function "test_function" line 7 at RETURN NEXT
>
> ********** Error **********
>
> ERROR: set-valued function called in context that cannot accept a set
> SQL state: 0A000
> Context: PL/pgSQL function "test_function" line 7 at RETURN NEXT
>
>
>
> PostgreSQL doesn't seem to see 'R' as being a
> SET OF RECORD....
>
>
> Peter
Did you happen to catch this:
Note that functions using RETURN NEXT or RETURN QUERY must be called as a table source in a FROM clause
Try:
select * from test_function(1)
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FUNCTION problem
2009-04-01 23:31 FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-01 23:55 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 16:09 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-02 22:20 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
@ 2009-04-02 23:22 ` Peter Willis <peterw@borstad.com>
2009-04-03 01:16 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 13+ messages in thread
From: Peter Willis @ 2009-04-02 23:22 UTC (permalink / raw)
To: Adrian Klaver <aklaver@comcast.net>; +Cc: pgsql-sql
Adrian Klaver wrote:
>
>
> Did you happen to catch this:
> Note that functions using RETURN NEXT or RETURN QUERY must be called as a table source in a FROM clause
>
> Try:
> select * from test_function(1)
>
I did miss that, but using that method to query the function
didn't work either. Postgres doesn't see the result as a
tabular set of records.
Even if I replace the FOR loop with:
<quote>
FOR R IN SELECT * FROM pg_database LOOP
RETURN NEXT R;
END LOOP;
</quote>
I get the same error(s). I don't think postgres likes
the unrelated 'SELECT INTO <variable> [column] FROM [QUERY] LIMIT 1'
lines before the FOR loop...
I think I need to go back and approach the function from a
different direction.
Thanks for all the pointers.
Peter
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FUNCTION problem
2009-04-01 23:31 FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-01 23:55 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 16:09 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-02 22:20 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 23:22 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
@ 2009-04-03 01:16 ` Adrian Klaver <aklaver@comcast.net>
2009-04-03 13:51 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2009-04-03 01:16 UTC (permalink / raw)
To: peterw@borstad.com; +Cc: pgsql-sql
On Thursday 02 April 2009 4:22:06 pm Peter Willis wrote:
> Adrian Klaver wrote:
> > Did you happen to catch this:
> > Note that functions using RETURN NEXT or RETURN QUERY must be called as a
> > table source in a FROM clause
> >
> > Try:
> > select * from test_function(1)
>
> I did miss that, but using that method to query the function
> didn't work either. Postgres doesn't see the result as a
> tabular set of records.
>
> Even if I replace the FOR loop with:
>
> <quote>
> FOR R IN SELECT * FROM pg_database LOOP
> RETURN NEXT R;
> END LOOP;
>
> </quote>
>
> I get the same error(s). I don't think postgres likes
> the unrelated 'SELECT INTO <variable> [column] FROM [QUERY] LIMIT 1'
> lines before the FOR loop...
>
> I think I need to go back and approach the function from a
> different direction.
>
> Thanks for all the pointers.
>
> Peter
Now I remember. Its something that trips me up, the RECORD in RETURN setof
RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a
better explanation-
http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
Note that RECORD is not a true data type, only a placeholder. One should also
realize that when a PL/pgSQL function is declared to return type record, this
is not quite the same concept as a record variable, even though such a function
might use a record variable to hold its result. In both cases the actual row
structure is unknown when the function is written, but for a function returning
record the actual structure is determined when the calling query is parsed,
whereas a record variable can change its row structure on-the-fly.
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FUNCTION problem
2009-04-01 23:31 FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-01 23:55 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 16:09 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-02 22:20 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 23:22 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-03 01:16 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
@ 2009-04-03 13:51 ` Adrian Klaver <aklaver@comcast.net>
2009-04-03 14:12 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2009-04-03 13:51 UTC (permalink / raw)
To: pgsql-sql; +Cc: peterw@borstad.com
On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote:
>
>
> Now I remember. Its something that trips me up, the RECORD in RETURN setof
> RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for
> a better explanation-
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PL
>PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a
> placeholder. One should also realize that when a PL/pgSQL function is
> declared to return type record, this is not quite the same concept as a
> record variable, even though such a function might use a record variable to
> hold its result. In both cases the actual row structure is unknown when the
> function is written, but for a function returning record the actual
> structure is determined when the calling query is parsed, whereas a record
> variable can change its row structure on-the-fly.
>
>
>
> --
> Adrian Klaver
> aklaver@comcast.net
For this particular case the following works.
CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record
AS $Body$
DECLARE croid integer;
DECLARE R RECORD;
BEGIN
SELECT INTO croid 2;
SELECT INTO R croid,$1;
RETURN R;
END;
$Body$
LANGUAGE plpgsql;
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FUNCTION problem
2009-04-01 23:31 FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-01 23:55 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 16:09 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-02 22:20 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 23:22 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-03 01:16 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 13:51 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
@ 2009-04-03 14:12 ` Adrian Klaver <aklaver@comcast.net>
2009-04-03 15:29 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
0 siblings, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2009-04-03 14:12 UTC (permalink / raw)
To: pgsql-sql; +Cc: peterw@borstad.com
On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote:
> On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote:
> > Now I remember. Its something that trips me up, the RECORD in RETURN
> > setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See
> > below for a better explanation-
> > http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#
> >PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type,
> > only a placeholder. One should also realize that when a PL/pgSQL function
> > is declared to return type record, this is not quite the same concept as
> > a record variable, even though such a function might use a record
> > variable to hold its result. In both cases the actual row structure is
> > unknown when the function is written, but for a function returning record
> > the actual structure is determined when the calling query is parsed,
> > whereas a record variable can change its row structure on-the-fly.
> >
> >
> >
> > --
> > Adrian Klaver
> > aklaver@comcast.net
>
> For this particular case the following works.
>
> CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record
> AS $Body$
> DECLARE croid integer;
> DECLARE R RECORD;
> BEGIN
> SELECT INTO croid 2;
> SELECT INTO R croid,$1;
> RETURN R;
> END;
>
> $Body$
> LANGUAGE plpgsql;
>
> --
> Adrian Klaver
> aklaver@comcast.net
Forgot to show how to call it.
test=# SELECT * from test_function(1) as test(c1 int,c2 int);
c1 | c2
----+----
2 | 1
(1 row)
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FUNCTION problem
2009-04-01 23:31 FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-01 23:55 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 16:09 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-02 22:20 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 23:22 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-03 01:16 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 13:51 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 14:12 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
@ 2009-04-03 15:29 ` Peter Willis <peterw@borstad.com>
2009-04-03 16:59 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 13+ messages in thread
From: Peter Willis @ 2009-04-03 15:29 UTC (permalink / raw)
To: Adrian Klaver <aklaver@comcast.net>; +Cc: pgsql-sql
Adrian Klaver wrote:
> On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote:
>> On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote:
>>> Now I remember. Its something that trips me up, the RECORD in RETURN
>>> setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See
>>> below for a better explanation-
>>> http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#
>>> PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type,
>>> only a placeholder. One should also realize that when a PL/pgSQL function
>>> is declared to return type record, this is not quite the same concept as
>>> a record variable, even though such a function might use a record
>>> variable to hold its result. In both cases the actual row structure is
>>> unknown when the function is written, but for a function returning record
>>> the actual structure is determined when the calling query is parsed,
>>> whereas a record variable can change its row structure on-the-fly.
>>>
>>>
>>>
>>> --
>>> Adrian Klaver
>>> aklaver@comcast.net
>> For this particular case the following works.
>>
>> CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record
>> AS $Body$
>> DECLARE croid integer;
>> DECLARE R RECORD;
>> BEGIN
>> SELECT INTO croid 2;
>> SELECT INTO R croid,$1;
>> RETURN R;
>> END;
>>
>> $Body$
>> LANGUAGE plpgsql;
>>
>> --
>> Adrian Klaver
>> aklaver@comcast.net
>
> Forgot to show how to call it.
>
> test=# SELECT * from test_function(1) as test(c1 int,c2 int);
> c1 | c2
> ----+----
> 2 | 1
> (1 row)
>
>
Ah!, I see what you mean about the definition of 'RECORD'.
(The lights come on...)
And here I thought it would all be so simple.....
You show a valid, and most informative solution.
This should get things working for me.
Thank you very much for your help.
Peter
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FUNCTION problem
2009-04-01 23:31 FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-01 23:55 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 16:09 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-02 22:20 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 23:22 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-03 01:16 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 13:51 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 14:12 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 15:29 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
@ 2009-04-03 16:59 ` Adrian Klaver <aklaver@comcast.net>
2009-04-03 19:10 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
0 siblings, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2009-04-03 16:59 UTC (permalink / raw)
To: peterw@borstad.com; +Cc: pgsql-sql
----- "Peter Willis" <peterw@borstad.com> wrote:
> Adrian Klaver wrote:
> > On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote:
> >> On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote:
> >>> Now I remember. Its something that trips me up, the RECORD in
> RETURN
> >>> setof RECORD is not the same thing as the RECORD in DECLARE
> RECORD. See
> >>> below for a better explanation-
> >>>
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#
> >>> PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data
> type,
> >>> only a placeholder. One should also realize that when a PL/pgSQL
> function
> >>> is declared to return type record, this is not quite the same
> concept as
> >>> a record variable, even though such a function might use a record
> >>> variable to hold its result. In both cases the actual row
> structure is
> >>> unknown when the function is written, but for a function returning
> record
> >>> the actual structure is determined when the calling query is
> parsed,
> >>> whereas a record variable can change its row structure
> on-the-fly.
> >>>
> >>>
> >>>
> >>> --
> >>> Adrian Klaver
> >>> aklaver@comcast.net
> >> For this particular case the following works.
> >>
> >> CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record
> >> AS $Body$
> >> DECLARE croid integer;
> >> DECLARE R RECORD;
> >> BEGIN
> >> SELECT INTO croid 2;
> >> SELECT INTO R croid,$1;
> >> RETURN R;
> >> END;
> >>
> >> $Body$
> >> LANGUAGE plpgsql;
> >>
> >> --
> >> Adrian Klaver
> >> aklaver@comcast.net
> >
> > Forgot to show how to call it.
> >
> > test=# SELECT * from test_function(1) as test(c1 int,c2 int);
> > c1 | c2
> > ----+----
> > 2 | 1
> > (1 row)
> >
> >
>
> Ah!, I see what you mean about the definition of 'RECORD'.
> (The lights come on...)
>
> And here I thought it would all be so simple.....
>
> You show a valid, and most informative solution.
> This should get things working for me.
If you are using Postgres 8.1+ then it becomes even easier because you can use OUT parameters in the function argument list to eliminate the "as test(c1 int,c2 int)" clause. At this point it becomes a A-->B-->C problem i.e determine what your inputs are, how you want to process them and how you want to return the output.
>
> Thank you very much for your help.
>
> Peter
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: FUNCTION problem
2009-04-01 23:31 FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-01 23:55 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 16:09 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-02 22:20 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 23:22 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-03 01:16 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 13:51 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 14:12 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 15:29 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-03 16:59 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
@ 2009-04-03 19:10 ` Peter Willis <peterw@borstad.com>
0 siblings, 0 replies; 13+ messages in thread
From: Peter Willis @ 2009-04-03 19:10 UTC (permalink / raw)
To: Adrian Klaver <aklaver@comcast.net>; +Cc: pgsql-sql
Adrian Klaver wrote:
>
>
> If you are using Postgres 8.1+ then it becomes even easier because you can use OUT parameters in the function argument list to eliminate the "as test(c1 int,c2 int)" clause. At this point it becomes a A-->B-->C problem i.e determine what your inputs are, how you want to process them and how you want to return the output.
>
'8.1+'?? Hmmm, I'm using 8.3. I could use that.
I got the more complex version of the query to work
by backing away from 'plpgsql' as the language and using
'sql' instead.
I then nested (terribly ugly) my select statements to
generate a single SQL query from all. This allows
me to change the output of the query without needing
to define a new set of output 'OUT' parameters each time
I change things.
I have use of the 'OUT' parameters with another set
of functions though. Thanks for that.
Peter
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2009-04-03 19:10 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-21 12:25 function problem geraldo <geraldo@umc.br>
2003-10-21 19:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-01 23:31 FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-01 23:55 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 16:09 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-02 22:20 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-02 23:22 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-03 01:16 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 13:51 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 14:12 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 15:29 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
2009-04-03 16:59 ` Re: FUNCTION problem Adrian Klaver <aklaver@comcast.net>
2009-04-03 19:10 ` Re: FUNCTION problem Peter Willis <peterw@borstad.com>
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