Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 79B2D1337B9C; Thu, 27 Jan 2011 04:02:30 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 50270-02; Thu, 27 Jan 2011 08:02:22 +0000 (UTC) X-Greylist: delayed 00:10:05.265781 by SQLgrey-1.7.6 X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sj-iport-6.cisco.com (sj-iport-6.cisco.com [171.71.176.117]) by mail.postgresql.org (Postfix) with ESMTP id 9BA871337B96; Thu, 27 Jan 2011 04:02:21 -0400 (AST) Authentication-Results: sj-iport-6.cisco.com; dkim=neutral (message not signed) header.i=none X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AgkFALKyQE1AaMHG/2dsb2JhbACCQqI0c6AVmzmFTwSFFopj Received: from syd-core-1.cisco.com ([64.104.193.198]) by sj-iport-6.cisco.com with ESMTP; 27 Jan 2011 07:52:13 +0000 Received: from xbh-bgl-411.cisco.com (xbh-bgl-411.cisco.com [72.163.129.201]) by syd-core-1.cisco.com (8.13.8/8.14.3) with ESMTP id p0R7qAcg003429; Thu, 27 Jan 2011 07:52:12 GMT Received: from xmb-bgl-416.cisco.com ([72.163.129.212]) by xbh-bgl-411.cisco.com with Microsoft SMTPSVC(6.0.3790.4675); Thu, 27 Jan 2011 13:22:11 +0530 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CBBDF7.1ACE5E99" Subject: Queyring for columns which are exist in table. Date: Thu, 27 Jan 2011 13:22:10 +0530 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Queyring for columns which are exist in table. Thread-Index: Acu8jPRkBAGMqG+hTkS33EqkvNF/xABafUVg From: "Santosh Bhujbal (sabhujba)" To: "Santosh Bhujbal (sabhujba)" , , X-OriginalArrivalTime: 27 Jan 2011 07:52:11.0721 (UTC) FILETIME=[1AF7CB90:01CBBDF7] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 201101/770 X-Sequence-Number: 172129 This is a multi-part message in MIME format. ------_=_NextPart_001_01CBBDF7.1ACE5E99 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hi All, =20 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. =20 SETUP details: Platform : Sun Solaris 5.10 Postgres : 8.3.7 =20 CREATE TABLE tbl ( c1 integer, c2 integer, c3 integer ); =20 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); =20 =20 CREATE OR REPLACE FUNCTION ColumnAlreadyExists(name, name) RETURNS INTEGER AS E' DECLARE columnCount INTEGER; BEGIN =20 SELECT COUNT (pg_attribute.attname) into columnCount FROM pg_attribute,pg_class, pg_type WHERE ((pg_attribute.attrelid=3Dpg_class.oid) AND (pg_attribute.atttypid=3Dpg_type.oid) AND (pg_class.relname =3D $1) AND (pg_attribute.attname =3D $2)); =20 IF columnCount =3D 0 THEN RETURN 0; END IF; RETURN 1; END; ' LANGUAGE 'plpgsql'; =20 =20 DROP FUNCTION checkColumn(name,name,name); CREATE OR REPLACE FUNCTION checkColumn(name, name, name) RETURNS name AS E' DECLARE isColumnExist INTEGER; =20 BEGIN SELECT ColumnAlreadyExists ($1,$2) into isColumnExist; =20 IF isColumnExist =3D 0 THEN RETURN name($3); ELSE RETURN name($2); END IF; END; ' LANGUAGE 'plpgsql'; =20 =20 =20 Function checkColumn should return proper column name (second parameter) if column exist and third parameter if column not exist. =20 NOW when I try to execute following command it returns improper result. I expect proper column values as a output of query. =20 SELECT(checkColumn('tbl','c2','0'))::name FROM tbl; =20 mydb=3D# SELECT (checkColumn('tbl','c2','0'))::name FROM tbl; checkcolumn ------------- c2 c2 c2 c2 c2 c2 c2 c2 (8 rows) =20 mydb=3D# =20 =20 Above query should return actual values present for c2 column in tbl. But it's not working as desired. =20 Please help me in this. =20 Thanks in advance, Santosh. ------_=_NextPart_001_01CBBDF7.1ACE5E99 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

         &nbs= p;     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=3Dpg_class.oid) AND (pg_attribute.atttypid=3Dpg_type.oid) AND (pg_class.relname =3D $1) AND (pg_attribute.attname =3D $2));

 

IF columnCount =3D 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 =3D 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=3D# SELECT (checkColumn('tbl','c2','0'))::name FROM = tbl;

 checkcolumn

-------------

 c2

 c2

 c2

 c2

 c2

 c2

 c2

 c2

(8 rows)

 

mydb=3D#

 

 

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.

------_=_NextPart_001_01CBBDF7.1ACE5E99--