From: | "Santosh Bhujbal (sabhujba)" <sabhujba(at)cisco(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Queyring for columns which are exist in table. |
Date: | 2011-01-25 12:39:49 |
Message-ID: | B439CD362EF4414F83E2317F5EF6FD7F01E393DB@XMB-BGL-416.cisco.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
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.
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.
From | Date | Subject | |
---|---|---|---|
Next Message | John DeSoi | 2011-01-25 13:21:16 | Re: iPad and Pg revisited... |
Previous Message | Jasen Betts | 2011-01-25 12:21:26 | Re: Moving from SQL Anywhere to PostGres - First Time |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-01-25 15:06:46 | Re: Control reached end of trigger procedure without RETURN |
Previous Message | gargdevender74 | 2011-01-25 11:10:59 | create geometry by lat/long |