From: Sim Zacks <sim@compulab.co.il>
To: PostgreSQL general <pgsql-general@postgresql.org>
Subject: Re: Queyring for columns which are exist in table.
Date: Thu, 27 Jan 2011 16:15:37 +0200
Message-ID: <4D417E09.4080201@compulab.co.il> (raw)
In-Reply-To: <B439CD362EF4414F83E2317F5EF6FD7F01E396F8@XMB-BGL-416.cisco.com>
References: <B439CD362EF4414F83E2317F5EF6FD7F01E395F0@XMB-BGL-416.cisco.com>
<4D414F41.1020804@compulab.co.il>
<B439CD362EF4414F83E2317F5EF6FD7F01E396F8@XMB-BGL-416.cisco.com>
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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] *On Behalf Of *Sim Zacks
> *Sent:* Thursday, January 27, 2011 4:26 PM
> *To:* pgsql-general@postgresql.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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sim@compulab.co.il, pgsql-general@postgresql.org
Subject: Re: Queyring for columns which are exist in table.
In-Reply-To: <4D417E09.4080201@compulab.co.il>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox