|
Hi
All, I
want to fire a query such that if the particular column does not exist then
query should return some default value. For
that I have tried following experiment. SETUP details: Platform : Sun Solaris 5.10
Postgres : 8.3.7 CREATE
TABLE tbl (
c1 integer,
c2 integer,
c3 integer ); INSERT
INTO tbl VALUES (1, 2, 3); INSERT
INTO tbl VALUES (2, 3, 4); INSERT
INTO tbl VALUES (3, 4, 5); INSERT
INTO tbl VALUES (4, 5, 6); INSERT
INTO tbl VALUES (5, 6, 7); INSERT
INTO tbl VALUES (6, 7, 8); INSERT
INTO tbl VALUES (7, 8, 9); INSERT
INTO tbl VALUES (8, 9, 10); CREATE
OR REPLACE FUNCTION ColumnAlreadyExists(name, name) RETURNS INTEGER AS E' DECLARE
columnCount INTEGER; BEGIN SELECT
COUNT (pg_attribute.attname) into columnCount FROM pg_attribute,pg_class,
pg_type WHERE ((pg_attribute.attrelid=pg_class.oid) AND
(pg_attribute.atttypid=pg_type.oid) AND (pg_class.relname = $1) AND
(pg_attribute.attname = $2)); IF
columnCount = 0 THEN RETURN
0; END
IF; RETURN
1; END; '
LANGUAGE 'plpgsql'; DROP
FUNCTION checkColumn(name,name,name); CREATE
OR REPLACE FUNCTION checkColumn(name, name, name) RETURNS name AS E' DECLARE
isColumnExist INTEGER; BEGIN SELECT
ColumnAlreadyExists ($1,$2) into isColumnExist; IF
isColumnExist = 0 THEN RETURN
name($3); ELSE RETURN
name($2); END
IF; END; '
LANGUAGE 'plpgsql'; Function
checkColumn should return proper column name (second parameter) if column exist
and third parameter if column not exist. NOW
when I try to execute following command it returns improper result. I
expect proper column values as a output of query. SELECT(checkColumn('tbl','c2','0'))::name
FROM tbl; mydb=#
SELECT (checkColumn('tbl','c2','0'))::name FROM tbl; checkcolumn ------------- c2 c2 c2 c2 c2 c2 c2 c2 (8
rows) mydb=# Above
query should return actual values present for c2 column in tbl. But
it’s not working as desired. Please
help me in this. Thanks
in advance, Santosh. |
