plpgsql-fct. fails on NULL in record variables
От | Daniel Martini |
---|---|
Тема | plpgsql-fct. fails on NULL in record variables |
Дата | |
Msg-id | 1096018380.4153e9cc855f9@webmail.uni-hohenheim.de обсуждение исходный текст |
Список | pgsql-general |
Hi all, I'm currently coding some functions in plpgsql for generating reports out of records in a table. Problem is: NULL values in records make the complete function fail. Here is a simple test case (original is more complex with a multi- table query in the for qres in select... part): create table test( id serial, descr char(4), data int ); insert into test (descr, data) values ('set1', 15); -- record 2 does not have a data value -- insert into test (descr) values ('set2'); create function report(int) returns text as ' declare qres record; report text; begin for qres in select descr, data from test where id=$1 loop report:=qres.descr||'': ''||qres.data; end loop; return report; end;' language 'plpgsql'; now test the function in psql: test=> select report(1); report ---------- set1: 15 (1 row) test=> select report(2); report -------- (1 row) while what I want it to return in the second case is this: report -------- set2: (1 row) 'set2: NULL' would be ok as well. How can I achieve this? Regards, Daniel
В списке pgsql-general по дате отправления: