pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQueyring for columns which are exist in table.
8+ messages / 4 participants
[nested] [flat]
* Queyring for columns which are exist in table.
@ 2011-01-25 12:39 Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>
0 siblings, 0 replies; 8+ messages in thread
From: Santosh Bhujbal (sabhujba) @ 2011-01-25 12:39 UTC (permalink / raw)
To: pgsql-sql; pgsql-general@postgresql.org
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.
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=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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queyring for columns which are exist in table.
@ 2011-01-26 07:17 msi77 <msi77@yandex.ru>
0 siblings, 0 replies; 8+ messages in thread
From: msi77 @ 2011-01-26 07:17 UTC (permalink / raw)
To: pgsql-sql
Why would not use information schema to checking of existence of some column in a table:
select column_name from information_schema.columns where table_name='tbl'
^ permalink raw reply [nested|flat] 8+ messages in thread
* Queyring for columns which are exist in table.
@ 2011-01-27 07:52 Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>
0 siblings, 2 replies; 8+ messages in thread
From: Santosh Bhujbal (sabhujba) @ 2011-01-27 07:52 UTC (permalink / raw)
To: Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>; pgsql-sql; pgsql-general@postgresql.org
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
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=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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queyring for columns which are exist in table.
@ 2011-01-27 09:38 Thom Brown <thom@linux.com>
parent: Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>
1 sibling, 1 reply; 8+ messages in thread
From: Thom Brown @ 2011-01-27 09:38 UTC (permalink / raw)
To: Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>; +Cc: pgsql-sql; pgsql-general@postgresql.org
On 27 January 2011 07:52, Santosh Bhujbal (sabhujba) <sabhujba@cisco.com> wrote:
> Hi All,
>
>
>
> I want to fire a query such that if the particular column does not exist
> then query should return some default value.
Why do you want to do this? What is it you using this for?
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queyring for columns which are exist in table.
@ 2011-01-27 09:53 Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>
parent: Thom Brown <thom@linux.com>
0 siblings, 1 reply; 8+ messages in thread
From: Santosh Bhujbal (sabhujba) @ 2011-01-27 09:53 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-sql; pgsql-general@postgresql.org
Hi Thom,
Thank you for your response.
I have a application which is periodically gathering diff stats from
diff devices and put them into database.
Tables are created per stat, per device and per day.
e.g. stat1_dev1_20100125, stat1_dev1_20100126, stat1_dev1_20100127, etc.
stat1_dev2_20100125, stat1_dev2_20100126, stat1_dev2_20100127, etc.
stat2_dev1_20100125, stat2_dev1_20100126, stat2_dev1_20100127, etc.
stat2_dev2_20100125, stat2_dev2_20100126, stat2_dev2_20100127, etc.
Now when I am upgrading my application with new version then there are
some tables which are having some additional columns.
In this case I have to alter each and every old tables in database with
new column and it's default value.
As there are large number of tables, the upgrade process is taking too
much time (in days).
To avoid above upgrade process I want to write a SQL statements such
that it take care of newly added columns.
Thanks,
Santosh.
-----Original Message-----
From: Thom Brown [mailto:thom@linux.com]
Sent: Thursday, January 27, 2011 3:09 PM
To: Santosh Bhujbal (sabhujba)
Cc: pgsql-sql@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Queyring for columns which are exist in table.
On 27 January 2011 07:52, Santosh Bhujbal (sabhujba)
<sabhujba@cisco.com> wrote:
> Hi All,
>
>
>
> I want to fire a query such that if the particular column does not
exist
> then query should return some default value.
Why do you want to do this? What is it you using this for?
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queyring for columns which are exist in table.
@ 2011-01-27 10:08 Thom Brown <thom@linux.com>
parent: Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>
0 siblings, 0 replies; 8+ messages in thread
From: Thom Brown @ 2011-01-27 10:08 UTC (permalink / raw)
To: Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>; +Cc: pgsql-sql; pgsql-general@postgresql.org
On 27 January 2011 09:53, Santosh Bhujbal (sabhujba) <sabhujba@cisco.com> wrote:
> Hi Thom,
>
> Thank you for your response.
>
> I have a application which is periodically gathering diff stats from
> diff devices and put them into database.
> Tables are created per stat, per device and per day.
> e.g. stat1_dev1_20100125, stat1_dev1_20100126, stat1_dev1_20100127, etc.
> stat1_dev2_20100125, stat1_dev2_20100126, stat1_dev2_20100127, etc.
> stat2_dev1_20100125, stat2_dev1_20100126, stat2_dev1_20100127, etc.
> stat2_dev2_20100125, stat2_dev2_20100126, stat2_dev2_20100127, etc.
>
> Now when I am upgrading my application with new version then there are
> some tables which are having some additional columns.
> In this case I have to alter each and every old tables in database with
> new column and it's default value.
> As there are large number of tables, the upgrade process is taking too
> much time (in days).
>
> To avoid above upgrade process I want to write a SQL statements such
> that it take care of newly added columns.
You want to refer to a column which doesn't exist, but PostgreSQL
expects you to know what is available beforehand. Is there any logic
to which tables have the additional column and which ones don't? For
example, do all tables with the additional column have a name
containing a date after a certain point in time?
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queyring for columns which are exist in table.
@ 2011-01-27 10:56 Sim Zacks <sim@compulab.co.il>
parent: Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>
1 sibling, 1 reply; 8+ messages in thread
From: Sim Zacks @ 2011-01-27 10:56 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Queyring for columns which are exist in table.
@ 2011-01-27 14:15 Sim Zacks <sim@compulab.co.il>
parent: Sim Zacks <sim@compulab.co.il>
0 siblings, 0 replies; 8+ messages in thread
From: Sim Zacks @ 2011-01-27 14:15 UTC (permalink / raw)
To: PostgreSQL general <pgsql-general@postgresql.org>
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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2011-01-27 14:15 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-25 12:39 Queyring for columns which are exist in table. Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>
2011-01-26 07:17 Re: Queyring for columns which are exist in table. msi77 <msi77@yandex.ru>
2011-01-27 07:52 Queyring for columns which are exist in table. Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>
2011-01-27 09:38 ` Thom Brown <thom@linux.com>
2011-01-27 09:53 ` Santosh Bhujbal (sabhujba) <sabhujba@cisco.com>
2011-01-27 10:08 ` Thom Brown <thom@linux.com>
2011-01-27 10:56 ` Sim Zacks <sim@compulab.co.il>
2011-01-27 14:15 ` Sim Zacks <sim@compulab.co.il>
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