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-ow...@postgresql.org
[mailto:pgsql-general-ow...@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
