Skip site navigation (1) Skip section navigation (2)

Queyring for columns which are exist in table.

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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.

pgsql-sql by date

Next:From: Tom LaneDate: 2011-01-25 15:06:46
Subject: Re: Control reached end of trigger procedure without RETURN
Previous:From: gargdevender74Date: 2011-01-25 11:10:59
Subject: create geometry by lat/long

pgsql-general by date

Next:From: John DeSoiDate: 2011-01-25 13:21:16
Subject: Re: iPad and Pg revisited...
Previous:From: Jasen BettsDate: 2011-01-25 12:21:26
Subject: Re: Moving from SQL Anywhere to PostGres - First Time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group