Re: Returning with a userd defined type (PL/pgSQL)

From: Együd Csaba <csegyud(at)freemail(dot)hu>
To: "Joe Conway" <mail(at)joeconway(dot)com>
Cc: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Returning with a userd defined type (PL/pgSQL)
Date: 2003-04-25 08:21:20
Message-ID: 000701c30b03$da4b9620$200a0a0a@notebook
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> It isn't clear (at least to me) what you are asking. Can you provide a
> self-contained example function along with supporting information (table
> definitions, etc.) and the error message you are getting?

Hi Joe,

Sorry, I should have provided these information before.
I want to filter the rows of a table and give some additional information
calculated for every row.
(I know that I do something wrong but I couldn'n find anything in the
documentation similar to this.)

An example: (on Postgres 7.3.2, RedHat 7.1)
----------------------------------------------------------------------------
-----------------------
create table t (id integer, name char(32));

create type MY_TYPE as (tid int, tname char(32), additional_info int);

create or replace function "my_func" () returns setof MY_TYPE as'
declare
R record;
ResultR MY_TYPE;
begin
for R in execute ''select * from t where id > 20'' loop
-- I know it has no meaning, but it can demonstrate what I want: simply
-- extending the information retrieved from the table.
ResultR.tid := R.id;
ResultR.tname := R.name;
ResultR.additional_info := R.id * 2;

return next ResultR;
end loop;
return;
end;
'LANGUAGE 'plpgsql';

pg732=# insert into t values (1,'name1');
INSERT 39602 1
pg732=# insert into t values (2,'name2');
INSERT 39603 1
pg732=# insert into t values (20,'name20');
INSERT 39604 1
pg732=# insert into t values (21,'name21');
INSERT 39605 1
pg732=# insert into t values (22,'name22');
INSERT 39606 1
pg732=# select * from my_func();
WARNING: plpgsql: ERROR during compile of my_func near line 12
ERROR: Incorrect argument to RETURN NEXT at or near "ResultR"
----------------------------------------------------------------------------
-------------------

The last command should result in something similar:

id | name | additional_info
----+----------------------------------+-----------------
21 | name21 | 42
22 | name22 | 44
(2 rows)

Thanks

Csaba

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2003-04-25 08:38:50 Re: Help with a query
Previous Message jose antonio leo 2003-04-25 07:41:27 Help with a query