Re: Returning with a userd defined type (PL/pgSQL)
От | Együd Csaba |
---|---|
Тема | Re: Returning with a userd defined type (PL/pgSQL) |
Дата | |
Msg-id | 000701c30b03$da4b9620$200a0a0a@notebook обсуждение исходный текст |
Ответ на | Returning with a userd defined type (PL/pgSQL) (Együd Csaba <csegyud@freemail.hu>) |
Ответы |
Re: Returning with a userd defined type (PL/pgSQL)
|
Список | 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
В списке pgsql-general по дате отправления: