Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id A61921337BA3 for ; Thu, 27 Jan 2011 06:56:27 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 23940-06 for ; Thu, 27 Jan 2011 10:56:20 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from compulab.co.il (compulab.co.il [67.18.134.219]) by mail.postgresql.org (Postfix) with ESMTP id 125B61337B9D for ; Thu, 27 Jan 2011 06:56:18 -0400 (AST) Received: from [62.90.235.247] (helo=zimbra-mta.compulab.co.il) by compulab.site5.com with esmtp (Exim 4.69) (envelope-from ) id 1PiPWT-0006nG-CQ for pgsql-general@postgresql.org; Thu, 27 Jan 2011 04:56:18 -0600 Received: from localhost (localhost.localdomain [127.0.0.1]) by zimbra-mta.compulab.co.il (Postfix) with ESMTP id A443D7E8E4D for ; Thu, 27 Jan 2011 12:56:14 +0200 (IST) X-Virus-Scanned: amavisd-new at compulab.co.il Received: from zimbra-mta.compulab.co.il ([127.0.0.1]) by localhost (zimbra-mta.compulab.co.il [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id p3jP+vUilLFU for ; Thu, 27 Jan 2011 12:56:12 +0200 (IST) Received: from [10.1.1.27] (sim-pc.compulab.local [10.1.1.27]) by zimbra-mta.compulab.co.il (Postfix) with ESMTP id AD2A57E91E6 for ; Thu, 27 Jan 2011 12:56:12 +0200 (IST) Message-ID: <4D414F41.1020804@compulab.co.il> Date: Thu, 27 Jan 2011 12:56:01 +0200 From: Sim Zacks User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.14pre) Gecko/20110118 Lightning/1.0b2 Shredder/3.1.8pre MIME-Version: 1.0 To: pgsql-general@postgresql.org Subject: Re: Queyring for columns which are exist in table. References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------070500070307060604080700" X-ACL-Warn: { X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - compulab.site5.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - compulab.co.il X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_FILL_THIS_FORM_SHORT=0.01, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201101/775 X-Sequence-Number: 172134 This is a multi-part message in MIME format. --------------070500070307060604080700 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 01/27/2011 09:52 AM, Santosh Bhujbal (sabhujba) wrote: > 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. > the name datatype won't cast it into a selectable column. Forgetting about your function, if you try select 'c2'::name from tbl it will also return c2 as a name datatype. What you want to do is a dynamic query where you use execute or for row in execute in a plpgsql function. Also to see if the column exists, I would recommend using information_schema.columns --------------070500070307060604080700 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit

On 01/27/2011 09:52 AM, Santosh Bhujbal (sabhujba) wrote:

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.

the name datatype won't cast it into a selectable column.
Forgetting about your function, if you try select 'c2'::name from tbl it will also return c2 as a name datatype.
What you want to do is a dynamic query where you use execute or for row in execute in a plpgsql function.

Also to see if the column exists, I would recommend using information_schema.columns

--------------070500070307060604080700--