Re: [GENERAL] Substitute a variable in PL/PGSQL.
Roberts, Jon wrote: What I am trying to do is find the difference between two tables, one that stores the information in a single column, and the other which stores the same data in multiple columns. E.g. CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 text, col6 text, col7 text, col8 text, col9 text, col10 text); CREATE TABLE test2(col_data text NOT NULL, some_data text NOT NULL, other_data text, CONSTRAINT test2_index PRIMARY KEY( col_data, some_data )); Trying to find data set in test2.col_data that is not in test.col1 to test.col10. FINALLY you get to the requirements. Next time, just ask a question like the above. You were asking how to solve a technical problem that didn't relate to the actual business need. Here are three ways to skin this cat. --version 1 select col_data from test2 except select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') || coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') || coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') || coalesce(col10, '') from test --version 2 select col_data from test2 t2 where not exists (select null from test t where t2.col_data = coalesce(t.col1, '') || coalesce(t.col2, '') || coalesce(t.col3, '') || coalesce(t.col4, '') || coalesce(t.col5, '') || coalesce(t.col6, '') || coalesce(t.col7, '') || coalesce(t.col8, '') || coalesce(t.col9, '') || coalesce(t.col10, '')) --version 3 select t2.col_data from test2 t2 left join (select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') || coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') || coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') || coalesce(col10, '') as col_data from test) t on t2.col_data = t.col_data where t.col_data is null Jon Thanks Jon for the hints. Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Substitute a variable in PL/PGSQL.
Klint Gore wrote: Steve Martin wrote: I am trying to create a PL/PGSQL function to return the values of the fields in a record, e.g. 1 value per row in the output of the function. How do you substitute a variable? CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$ DECLARE ted varchar; bob RECORD; BEGIN FOR bob IN SELECT * FROM test LOOP FOR i IN 1..10 LOOP ted := 'bob.col' || i; RETURN NEXT ted; END LOOP; END LOOP; RETURN; END $$ LANGUAGE plpgsql; Or is there another way other than using another procedural language. Thanks - Steve M. There's no direct way to reference a particular field in a record variable where the field name is held in a variable in pl/pgsql. I.E. if ted = 'col1' there's no way to reference bob.ted to give you the value of bob.col1. If you want it easy to code but have to create something for every table and modify it ever time the table changes create view test_vertical_table as select col1::text from test union all select col2::text from test union all select col3::text from test union all select col4::text from test union all select col5::text from test ... If you want to go the generic function route CREATE OR REPLACE FUNCTION testfunc(text) RETURNS SETOF text AS $$ DECLAREvertTableName alias for $1; ted text; bob RECORD; bill record; BEGIN for bill inselect table_name, column_namefrom information_schema.columnswhere table_schema = public and table_name = vertTableName loop FOR bob INexecute 'SELECT '||bill.column_name||' as thiscol FROM '||bill.table_nameLOOP ted := bob.thiscol; RETURN NEXT ted; END LOOP; end loop; RETURN; END $$ LANGUAGE plpgsql; klint. Hi Klint, Thanks for the advice, I found the sql to get the column names useful. Steve M. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Substitute a variable in PL/PGSQL.
[EMAIL PROTECTED] wrote: You can do it in straight sql like so. SELECT (array[col1, col2, col3, col4, col5, col6, col7, col8, col9, col10])[i] FROM test t, generate_series(1,10) i Art Hi Art, Thanks for the advice, in my case using arrays was not a option as the data could be null. Steve M. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Substitute a variable in PL/PGSQL.
Hi Francisco, Francisco Reyes wrote: On 12:33 am 07/22/08 Steve Martin [EMAIL PROTECTED] wrote: Hi, I am trying to create a PL/PGSQL function to return the values of the fields in a record, e.g. 1 value per row in the output of the function. Are you trying to do a generic function that would work for any table or for just a single table? Is it goint to run against a large data set? What I am trying to do is find the difference between two tables, one that stores the information in a single column, and the other which stores the same data in multiple columns. E.g. CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 text, col6 text, col7 text, col8 text, col9 text, col10 text); CREATE TABLE test2(col_data text NOT NULL, some_data text NOT NULL, other_data text, CONSTRAINT test2_index PRIMARY KEY( col_data, some_data )); Trying to find data set in test2.col_data that is not in test.col1 to test.col10. The data sets are very small, e.g. 10 000 rows. Using pl/pgsql. the tried using the pl/pgsql's EXECUTE statement, CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$ DECLARE ted text; bob RECORD; BEGIN FOR bob IN SELECT * FROM test LOOP FOR i IN 1..10 LOOP ted := 'bob.col' || i; EXECUTE 'RETURN NEXT ' || ted; -- RETURN NEXT bob.col1; END LOOP; END LOOP; RETURN; END $$ LANGUAGE plpgsql; test= select * from testfunc() ; ERROR: syntax error at or near RETURN at character 1 QUERY: RETURN NEXT bob.col1 CONTEXT: PL/pgSQL function testfunc line 8 at execute statement LINE 1: RETURN NEXT bob.col1 ^ test= Note Postgres version 8.1.10. Regards Steve Martin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Substitute a variable in PL/PGSQL.
Merlin Moncure wrote:
On Wed, Jul 23, 2008 at 4:08 AM, Klint Gore [EMAIL PROTECTED] wrote:
here is a way to do it with record variables...no inner loop but
doesn't the column names. with a little work you could add those with
some queries to information_schema (i don't think it's worth it
though).
create or replace function ff(tablename text) returns setof text as
$$
declare
r record;
begin
for r in
execute 'select record_out(' || tablename || ') as f' ||
' from ' || tablename loop
return next r.f;
end loop;
end;
$$ language plpgsql;
merlin
Hi Merlin,
Where can I find out more on the record_out function?
Steve M.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Substitute a variable in PL/PGSQL.
Steve Martin wrote:
Hi,
I am trying to create a PL/PGSQL function to return the values of the
fields in a record, e.g. 1 value per row in the output of the function.
How do you substitute a variable?
Test case:
CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5
text, col6 text, col7 text, col8 text, col9 text, col10 text);
INSERT INTO test VALUES ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i',
'j');
INSERT INTO test VALUES ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',
'J');
INSERT INTO test VALUES ('1', '2', '3', '4', '5', '6', '7', '8', '9',
'10');
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLAREted varchar;
bob RECORD;
BEGIN
FOR bob IN SELECT * FROM test LOOP
FOR i IN 1..10 LOOP
ted := 'bob.col' || i;
RETURN NEXT ted;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test= select * from testfunc();
testfunc ---
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
(30 rows)
test=
Or:
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE bob RECORD;
ted TEXT;
BEGIN
FOR i IN 1..10 LOOP
ted := 'col' || i;
FOR bob IN SELECT ted FROM test LOOP
RETURN NEXT bob;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test= select * from testfunc();
testfunc --
(col1)
(col1)
(col1)
(col2)
(col2)
(col2)
(col3)
(col3)
(col3)
(col4)
(col4)
(col4)
(col5)
(col5)
(col5)
(col6)
(col6)
(col6)
(col7)
(col7)
(col7)
(col8)
(col8)
(col8)
(col9)
(col9)
(col9)
(col10)
(col10)
(col10)
(30 rows)
test=
Or is there another way other than using another procedural language.
Thanks - Steve M.
Found that this function works if I process by column.
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
bob RECORD;
ted TEXT;
may TEXT;
BEGIN
FOR i IN 1..10 LOOP
ted := 'col' || i;
may := ' SELECT ' || ted || ' as col FROM test';
FOR bob IN EXECUTE may LOOP
RETURN NEXT bob.col;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test= select testfunc as data from testfunc() ;
data
--
a
A
1
b
B
2
c
C
3
d
D
4
e
E
5
f
F
6
g
G
7 d
D
4
e
E
5
f
F
6
g
G
7
h
H
8
i
I
9
j
J
10
(30 rows)
test=
Any ideas on how to process by row?
Steve Martin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Substitute a variable in PL/PGSQL.
Hi,
I am trying to create a PL/PGSQL function to return the values of the
fields in a record, e.g. 1 value per row in the output of the function.
How do you substitute a variable?
Test case:
CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5 text,
col6 text, col7 text, col8 text, col9 text, col10 text);
INSERT INTO test VALUES ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j');
INSERT INTO test VALUES ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J');
INSERT INTO test VALUES ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10');
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
ted varchar;
bob RECORD;
BEGIN
FOR bob IN SELECT * FROM test LOOP
FOR i IN 1..10 LOOP
ted := 'bob.col' || i;
RETURN NEXT ted;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test= select * from testfunc();
testfunc
---
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
bob.col1
bob.col2
bob.col3
bob.col4
bob.col5
bob.col6
bob.col7
bob.col8
bob.col9
bob.col10
(30 rows)
test=
Or:
CREATE OR REPLACE FUNCTION testfunc() RETURNS SETOF text AS $$
DECLARE
bob RECORD;
ted TEXT;
BEGIN
FOR i IN 1..10 LOOP
ted := 'col' || i;
FOR bob IN SELECT ted FROM test LOOP
RETURN NEXT bob;
END LOOP;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
test= select * from testfunc();
testfunc
--
(col1)
(col1)
(col1)
(col2)
(col2)
(col2)
(col3)
(col3)
(col3)
(col4)
(col4)
(col4)
(col5)
(col5)
(col5)
(col6)
(col6)
(col6)
(col7)
(col7)
(col7)
(col8)
(col8)
(col8)
(col9)
(col9)
(col9)
(col10)
(col10)
(col10)
(30 rows)
test=
Or is there another way other than using another procedural language.
Thanks - Steve M.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] timestamp with time zone output incorrect
Tom Lane wrote: Martijn van Oosterhout [EMAIL PROTECTED] writes: On Thu, Apr 24, 2008 at 06:30:27PM +1200, Steve Martin wrote: = show timezone ; TimeZone - NZST-12NZDT (1 row) I have no idea what timezone that it. Presumably it switches between daylight savings and non-daylight savings based on the US rules? Yeah, that's a POSIX zone spec. See http://www.postgresql.org/docs/8.3/static/datatype-datetime.html#DATATYPE-TIMEZONES As noted there, if the OP really really wants to spell his zone name that way, he could fool with the posixrules file in the timezone database. But Pacific/Auckland is probably better. (I don't remember whether 8.1 would honor changes in posixrules.) regards, tom lane Hi, Thanks Martijn and Tom for your feedback. Setting the timezone to Pacific/Auckland works. Re-read the document reference Tom pointed to and found I missed the comment about being wary of POSIX-style time zones. Thanks Steve Martin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] timestamp with time zone output incorrect
Hi, We are having trouble with the output of timestamp with time zone with versions 8.1.10 and 8.3.1. It seems reversed, and change over times are incorrect. timezone for both is: = show timezone ; TimeZone - NZST-12NZDT (1 row) Note, change over times for this year is: Sun Apr 06 02:59:59 NZDT 2008 -- Sun Apr 06 02:00:00 NZST 2008 Sun Sep 28 01:59:59 NZST 2008 -- Sun Sep 28 03:00:00 NZDT 2008 On both versions: = select timestamp with time zone '2008-01-01 00:00:00'; timestamptz 2008-01-01 00:00:00+12 = select timestamp with time zone '2008-05-01 00:00:00'; timestamptz 2008-05-01 00:00:00+13 (1 row) It seems that the time zone off set is reversed. Also it seems to be using the old change over times. = select timestamp with time zone '2008-03-09 01:00:00'; timestamptz 2008-03-09 01:00:00+12 (1 row) = select timestamp with time zone '2008-03-09 03:00:00'; timestamptz 2008-03-09 03:00:00+13 (1 row) Checked postgresql-8.3.1/src/timezone/data/australasia and the information here seems correct. The date on the system (HPUX 11.23) is correct, e.g. % date Thu Apr 24 18:22:42 NZST 2008 % echo $TZ NZST-12NZDT The database seems to know we are using the New Zealand time zone. It seems to think summer is coming it is winter. Any ideas anyone? Thanks Steve Martin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PQexec does not return.
Hi All, Found the problem. This was caused by a memory leak in our application. Regards Steve Martin Steve Martin wrote: Hi All, We have an intermittent problem where PQexec does not seem to return even though the server seems to have sent the results. From the gdb output , the sql statement can be seen, and from the log, the result can be seen to be sent. Both process are running on the same machine. Version of postgres and other info: 1. Postgres Version 8.1.4 2. Machine HP rx4640 3. OS: HPUX 11.23 4. Mem 8G Has anyone seen this type of problem before, can it be cause by a TCP/IP communication failure? From gdb: % /opt/langtools/bin/gdb /path/name -p 3587 HP gdb 5.2 for HP Itanium (32 or 64 bit) and target HP-UX 11.2x. Copyright 1986 - 2001 Free Software Foundation, Inc. Hewlett-Packard Wildebeest 5.2 (based on GDB) is covered by the GNU General Public License. Type show copying to see the conditions to change it and/or distribute copies. Type show warranty for warranty/support. .. Attaching to program: /path/name, process 3587 warning: No unwind information found. Skipping this library /usr/lib/hpux32/libcl.so.1. 0x6000c0342810:0 in _poll_sys+0x30 () from /usr/lib/hpux32/libc.so.1 (gdb) bt #0 0x6000c0342810:0 in _poll_sys+0x30 () from /usr/lib/hpux32/libc.so.1 #1 0x6000c03553e0:0 in poll+0x160 () from /usr/lib/hpux32/libc.so.1 #2 0x6000cefa75b0:0 in pqSocketCheck+0xb00 () from /usr/local/pgsql/lib/libpq.so.4 #3 0x6000cefa77c0:0 in pqWaitTimed+0x40 () from /usr/local/pgsql/lib/libpq.so.4 #4 0x6000cefa7890:0 in pqWait+0x40 () from /usr/local/pgsql/lib/libpq.so.4 #5 0x6000cefa53b0:0 in PQgetResult+0x180 () from /usr/local/pgsql/lib/libpq.so.4 #6 0x6000cefa56f0:0 in PQexecFinish+0x40 () from /usr/local/pgsql/lib/libpq.so.4 #7 0x6000c1c83870:0 in DBIFPostgreSelect::getRecord (this=0x40114840) at DBIFPostgre.C:1688 #8 0x6000c1c73d20:0 in DBIFPostgreSelect::selectNext (this=0x40114840, [EMAIL PROTECTED]) at DBIFPostgre.C:1902 #9 0x6000c1c64b90:0 in DBIFSelect::selectNext (this=0x7fff5240, [EMAIL PROTECTED]) at DBIF.C:2704 #10 0x404ff00:0 in TableStatus::checkDeferredTR (this=0x403104c0) at DbSContTable.C:1468 #11 0x405b430:0 in TableStatusManager::checkDeferredTR (this=0x400bde90) at DbSContTable.C:3146 #12 0x4068960:0 in Controller::go (this=0x7fffc320) at DbSController.C:1950 #13 0x406b1b0:0 in main (argc=1, argv=0x7fffed74) at DbSContMain.C:137 (gdb) q The program is running. Quit anyway (and detach it)? (y or n) y Detaching from program: /path/name, process 3587 % /opt/langtools/bin/gdb /path/name -p 3587 HP gdb 5.2 for HP Itanium (32 or 64 bit) and target HP-UX 11.2x. Copyright 1986 - 2001 Free Software Foundation, Inc. Hewlett-Packard Wildebeest 5.2 (based on GDB) is covered by the GNU General Public License. Type show copying to see the conditions to change it and/or distribute copies. Type show warranty for warranty/support. .. Attaching to program: /path/name, process 3587 warning: No unwind information found. Skipping this library /usr/lib/hpux32/libcl.so.1. 0x6000c0342810:0 in _poll_sys+0x30 () from /usr/lib/hpux32/libc.so.1 (gdb) up #1 0x6000c03553e0:0 in poll+0x160 () from /usr/lib/hpux32/libc.so.1 (gdb) up #2 0x6000cefa75b0:0 in pqSocketCheck+0xb00 () from /usr/local/pgsql/lib/libpq.so.4 (gdb) up #3 0x6000cefa77c0:0 in pqWaitTimed+0x40 () from /usr/local/pgsql/lib/libpq.so.4 (gdb) up #4 0x6000cefa7890:0 in pqWait+0x40 () from /usr/local/pgsql/lib/libpq.so.4 (gdb) up #5 0x6000cefa53b0:0 in PQgetResult+0x180 () from /usr/local/pgsql/lib/libpq.so.4 (gdb) up #6 0x6000cefa56f0:0 in PQexecFinish+0x40 () from /usr/local/pgsql/lib/libpq.so.4 (gdb) up #7 0x6000c1c83870:0 in DBIFPostgreSelect::getRecord (this=0x40114840) at DBIFPostgre.C:1688 1688myResultExecPrepare = PQexec(myConnection-conn, seleStmt); (gdb) p seleStmt $1 = SELECT * FROM T_AM_TERM_BILLING WHERE (TR_STATUS = 'DEFERRED') AND ((DOWNLOAD_DATE 20070108) OR ((DOWNLOAD_DATE = 20070108) AND (DOWNLOAD_TIME 203744))), '\000' repeats 43 times (gdb) q The program is running. Quit anyway (and detach it)? (y or n) y Detaching from program: /path/name, process 3587 From postgres log 2007-01-08 20:37:44.839 NZDT [EMAIL PROTECTED]LOG: statement: select pg_get_indexdef(indexrelid) from pg_index where ind relid = ( select oid from pg_class where relname = 't_am_registration_db') 2007-01-08 20:37:44.840 NZDT [EMAIL PROTECTED]LOG: duration: 0.347 ms
[GENERAL] PQexec does not return.
:44.841 NZDT [EMAIL PROTECTED]LOG: duration: 0.792 ms 2007-01-08 20:37:44.841 NZDT [EMAIL PROTECTED]LOG: statement: select attname, attnotnull, atttypid, atttypmod, format_ty pe(atttypid, atttypmod) as data_type from pg_attribute where attnum 0 and atttypid 0 and attrelid = ( select oid fro m pg_class where relname = 't_am_term_billing') order by attnum 2007-01-08 20:37:44.842 NZDT [EMAIL PROTECTED]LOG: duration: 0.778 ms 2007-01-08 20:37:44.842 NZDT [EMAIL PROTECTED]LOG: statement: select pg_get_indexdef(indexrelid) from pg_index where ind relid = ( select oid from pg_class where relname = 't_am_term_billing') 2007-01-08 20:37:44.842 NZDT [EMAIL PROTECTED]LOG: duration: 0.346 ms 2007-01-08 20:37:44.842 NZDT [EMAIL PROTECTED]LOG: statement: SELECT * FROM T_AM_TERM_BILLING WHERE (TR_STATUS = 'DEFERRED') AND ((DOWNLOAD_DATE 20070108) OR ((DOWNLOAD_DATE = 20070108) AND (DOWNLOAD_TIME 203744))) 2007-01-08 20:37:44.843 NZDT [EMAIL PROTECTED]LOG: duration: 1.165 ms 2007-01-08 20:50:02.026 NZDT @%LOG: autovacuum: processing database template1 2007-01-08 21:50:02.036 NZDT @%LOG: autovacuum: processing database smf 2007-01-08 22:50:02.084 NZDT @%LOG: autovacuum: processing database postgres 2007-01-08 23:50:02.104 NZDT @%LOG: autovacuum: processing database template1 2007-01-09 00:40:01.090 NZDT [EMAIL PROTECTED]LOG: connection received: host=[local] 2007-01-09 00:40:01.091 NZDT [EMAIL PROTECTED]LOG: connection authorized: user=ain database=smf 2007-01-09 00:40:01.093 NZDT [EMAIL PROTECTED]LOG: statement: select attname, attnotnull, atttypid, atttypmod, format_ty pe(atttypid, atttypmod) as data_type from pg_attribute where attnum 0 and atttypid 0 and attrelid = ( select oid fro m pg_class where relname = 'com_node_host') order by attnum 2007-01-09 00:40:01.097 NZDT [EMAIL PROTECTED]LOG: duration: 3.788 ms Regards Steve Martin
