Re: plpgsql-fct. fails on NULL in record variables
От | Najib Abi Fadel |
---|---|
Тема | Re: plpgsql-fct. fails on NULL in record variables |
Дата | |
Msg-id | 006601c4a23e$aeb90690$f664a8c0@najib обсуждение исходный текст |
Ответ на | plpgsql-fct. fails on NULL in record variables (Daniel Martini <dmartini@uni-hohenheim.de>) |
Список | pgsql-general |
concatenating a NULL value to a string will return NULL. SELECT NULL||'Stringggg'; ?column? ---------- (1 row) There's a fonction called coalesce that replaces NULL values with a specified value it can be usefull: SELECT coalesce(NULL,'') || ' Stringgggg'; ?column? ------------- Stringgggg (1 row) SELECT coalesce('A','') || ' Stringgggg'; ?column? -------------- A Stringgggg (1 row) HTH Najib. ----- Original Message ----- From: "Daniel Martini" <dmartini@uni-hohenheim.de> To: <pgsql-general@postgresql.org> Sent: Friday, September 24, 2004 11:33 AM Subject: [GENERAL] plpgsql-fct. fails on NULL in record variables > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match
В списке pgsql-general по дате отправления: