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

Re: Queyring for columns which are exist in table.

From: Sim Zacks <sim(at)compulab(dot)co(dot)il>
To: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Queyring for columns which are exist in table.
Date: 2011-01-27 14:15:37
Message-ID: 4D417E09.4080201@compulab.co.il (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-sql
What you will need to do in your function is


...

sql = 'select ' || checkColumn('tbl','c2','0') || '::text as fld from tbl';

for row in execute sql loop

     return next row.fld;

end loop;

return;

...


where the function returns a set of text (or int or whatever the 
datatype will always be.


Sim


On 01/27/2011 01:35 PM, Santosh Bhujbal (sabhujba) wrote:

> Hi Sim,
>
> Thank you for the response.
>
> My question is why is it not working?
>
> What changes needs to be done in function or in calling SQL to make it 
> work as per requirement?
>
> Thanks,
>
> Santosh.
>
> *From:*pgsql-general-owner(at)postgresql(dot)org 
> [mailto:pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Sim Zacks
> *Sent:* Thursday, January 27, 2011 4:26 PM
> *To:* pgsql-general(at)postgresql(dot)org
> *Subject:* Re: [GENERAL] Queyring for columns which are exist in table.
>
> 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
>

In response to

pgsql-sql by date

Next:From: Emi LuDate: 2011-01-27 14:32:09
Subject: Re: how to get row number in select query
Previous:From: Sim ZacksDate: 2011-01-27 10:56:01
Subject: Re: Queyring for columns which are exist in table.

pgsql-general by date

Next:From: Lawrence CohanDate: 2011-01-27 14:17:01
Subject: Re: Adding ddl audit trigger
Previous:From: hubert depesz lubaczewskiDate: 2011-01-27 14:12:45
Subject: Re: Select query ignores index on large table

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