Re: [SQL] Avoiding "will create implicit index" NOTICE
Hmm, no. I still get the NOTICE. How can I create the primary key without triggering a NOTICE? bnesbitt=> create unique index test_5_pkey on test_5 (userid, site_key); CREATE INDEX bnesbitt=> alter table test_5 add primary key (userid, site_key); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "test_5_pkey1" for table "test_5" ALTER TABLE bnesbitt=> \d test_5 Table "public.test_5" +---+-+---+ | Column | Type | Modifiers | +---+-+---+ | userid| integer | not null | | site_key | integer | not null | | ranking_365 | integer | default 0 | | downloads_total | integer | default 0 | | ranking_total | integer | default 0 | +---+-+---+ Indexes: "test_5_pkey1" PRIMARY KEY, btree (userid, site_key) "test_5_pkey" UNIQUE, btree (userid, site_key) Foreign-key constraints: "test_5_site_key_fkey" FOREIGN KEY (site_key) REFERENCES contexts(context_key) ON DELETE CASCADE "test_5_userid_fkey" FOREIGN KEY (userid) REFERENCES users(userid) ON DELETE CASCADE Bryce Nesbitt wrote: > Thanks, that's good. > > Rob Sargent wrote: > >> create table junk_six (foo int) >> create unique index junk_six_id on junk_six(foo) >> > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Avoiding "will create implicit index" NOTICE
In response to Bryce Nesbitt : > Hmm, no. I still get the NOTICE. How can I create the primary key > without triggering a NOTICE? Sure, set client_min_messages='...' test=*# create table bla(id int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bla_pkey" for table "bla" CREATE TABLE test=*# rollback; ROLLBACK test=# set client_min_messages='warning'; SET test=*# create table bla(id int primary key); CREATE TABLE test=*# Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] dynamic columns in a query
Hi All, Is there any way in postgres to write a query to display the result in matrix form. (where column names are dynamic) For eg. Employee Name Client1 Client2 Client3 Client4 Emp1100 102 90 23 Emp256 0 23 98 Emp334 45 76 0 Here Client1, Client2... are the values from the database. Thanks, Jyoti -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dynamic columns in a query
In response to Jyoti Seth : > Hi All, > > Is there any way in postgres to write a query to display the result in > matrix form. (where column names are dynamic) > > For eg. > > > Employee Name Client1 Client2 Client3 Client4 > Emp1 100 102 90 23 > Emp2 56 0 23 98 > Emp3 34 45 76 0 > > > Here Client1, Client2... are the values from the database. There is a contrib-modul, tablefunc. It contains a crosstab-function. Maybe this can help you. Other solution: write a function in plpgsql and build a string that contains your query, and EXECUTE that string. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dynamic columns in a query
Hello 2009/6/11 Jyoti Seth : > Hi All, > > Is there any way in postgres to write a query to display the result in > matrix form. (where column names are dynamic) > look on http://okbob.blogspot.com/2008/08/using-cursors-for-generating-cross.html regards Pavel Stehule > For eg. > > > Employee Name Client1 Client2 Client3 Client4 > Emp1 100 102 90 23 > Emp2 56 0 23 98 > Emp3 34 45 76 0 > > > Here Client1, Client2... are the values from the database. > > Thanks, > Jyoti > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Avoiding "will create implicit index" NOTICE
A. Kretschmer wrote: In response to Bryce Nesbitt : Hmm, no. I still get the NOTICE. How can I create the primary key without triggering a NOTICE? Sure, set client_min_messages='...' test=*# create table bla(id int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bla_pkey" for table "bla" CREATE TABLE test=*# rollback; ROLLBACK test=# set client_min_messages='warning'; SET test=*# create table bla(id int primary key); CREATE TABLE test=*# Andreas Funny thing is I'm using set client_min_message in my own scripts! Also was assuming OP was in a scripting/temp-table mode and that the notion of explicit primary key (vs. simply stating the index) isn't of great value in that realm I don't think. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] polymorphic function in 7.4 vs. 8.3
I have the following setup which works great in version 8.3 but throws an error in 7.4: CREATE TABLE atest1 ( id integer NOT NULL, descr text, CONSTRAINT atest1_pkey PRIMARY KEY (id) ); CREATE OR REPLACE FUNCTION test_trg() RETURNS "trigger" AS ' DECLARE any_rec wfsys.atest1; BEGIN any_rec.id := NEW.id; any_rec.descr := NEW.descr; select into any_rec * from dd_test(any_rec); --any_rec := dd_test(any_rec); RETURN any_rec; END; ' LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION dd_test(anyelement) RETURNS record AS ' DECLARE any_rec alias for $1; some_row record; BEGIN some_row := any_rec; if some_row.id < 0 then raise notice ''id is < 0!''; some_row.descr := ''some other value''; end if; RETURN some_row; END; ' LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER trg_atest1 BEFORE INSERT ON atest1 FOR EACH ROW EXECUTE PROCEDURE test_trg(); Finally, firing the trigger like so: insert into wfsys.atest1 values(123, 'some text'); Gives the following error: ERROR: column "any_rec" does not exist CONTEXT: PL/pgSQL function "test_trg" line 7 at select into variables ** Error ** ERROR: column "any_rec" does not exist SQL state: 42703 Context: PL/pgSQL function "test_trg" line 7 at select into variables It works great on 8.3 (my dev server), but throws the error on the machine I am forced to work with, which is running version 7.4. I realize that polymorphic functions were pretty new in v7.4, is there a workaround or am I making a silly mistake? Or both? Incidentally, I get the same error when I change the polymorphic function's argument from "anyelement" to "wfsys.atest1" so it seems that it is occurring in the trigger function. Thanks for any help. Richard -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] polymorphic function in 7.4 vs. 8.3
Richard Rosenberg writes: > I have the following setup which works great in version 8.3 but throws an > error in 7.4: I think you mangled your example to the point where it doesn't work in 8.3 either ... I get ERROR: a column definition list is required for functions returning "record" CONTEXT: SQL statement "select * from dd_test( $1 )" PL/pgSQL function "test_trg" line 7 at SQL statement regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] polymorphic function in 7.4 vs. 8.3
Richard Rosenberg writes: > Tom, thanks for your prompt reply. I think I may have my head on straight > now, > this should work: Yeah, but you're still out of luck on 7.4. Its plpgsql doesn't have any ability to pass whole-row variables into expressions. I don't see any answer for you except breaking down the row into columns, which of course is going to be a huge notational PITA. Sure you can't move the DB off 7.4? There would be pretty considerable benefits from adopting some recent release instead. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] polymorphic function in 7.4 vs. 8.3
Tom, thanks for your prompt reply. I think I may have my head on straight now, this should work: CREATE TABLE atest1 ( id integer NOT NULL, descr text, CONSTRAINT atest1_pkey PRIMARY KEY (id) ); CREATE OR REPLACE FUNCTION test1_trg() RETURNS trigger AS ' DECLARE some_rec public.atest1; BEGIN some_rec.id := NEW.id; some_rec.descr := NEW.descr; select into some_rec * from dd_test(some_rec) as (id int4, descr text); --some_rec := dd_test(some_rec); RETURN some_rec; END; ' LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION dd_test(anyelement) RETURNS record AS ' DECLARE any_row alias for $1; some_row record; BEGIN some_row := any_row; if some_row.id < 0 then raise notice ''id is < 0!''; some_row.descr := ''some other value''; end if; RETURN some_row; END; ' LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER trg_atest1 BEFORE INSERT ON atest1 FOR EACH ROW EXECUTE PROCEDURE test1_trg(); insert into public.atest1 values(123, 'some text'); insert into public.atest1 values(-90, 'some text'); This gives the same result. Also in the trigger function test1_trg the syntax of the call to the polymorphic function makes a difference in terms of the error that is thrown. A simple assignment like: . . . some_rec := dd_test(some_rec); . . . Throws a syntax error, while a 'SELECT INTO. . .' like: . . . select into some_rec * from dd_test(some_rec) as (id int4, descr text); . . . Throws this: ERROR: column "some_rec" does not exist SQL state: 42703 Context: PL/pgSQL function "test1_trg" line 7 at select into variables Sorry for the earlier typo(s), and thanks for any help. Richard -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] polymorphic function in 7.4 vs. 8.3
On Thursday 11 June 2009 14:49:46 Tom Lane wrote: > Sure you can't move the DB off 7.4? There would be pretty considerable > benefits from adopting some recent release instead. > > regards, tom lane Don't I know it. I am SOL as the machine is hosted/shared out by an external provider. I can do it by getting rid of the polymorphism - breaking the columns into separate args - as you say: CREATE OR REPLACE FUNCTION public.test1_trg() RETURNS "trigger" AS ' DECLARE some_rec public.atest1; BEGIN some_rec.id := NEW.id; some_rec.descr := NEW.descr; select into some_rec * from dd_test(some_rec.id, some_rec.descr, TG_RELNAME) as (id int, descr text); --some_rec := dd_test(some_rec)::public.atest1; RETURN some_rec; END; ' LANGUAGE 'plpgsql' VOLATILE; CREATE OR REPLACE FUNCTION public.dd_test(int, text, text) RETURNS record AS ' DECLARE any_id alias for $1; any_descr alias for $2; tablename alias for $3; some_id integer; some_descr text; some_row record; BEGIN some_id := any_id; if some_id < 0 then raise notice ''id is < 0!''; some_descr := ''some other value''; end if; for some_row in execute ''select * from ''||tablename||'' where 1 = 0'' loop end loop; some_row.id := some_id; some_row.descr := some_descr; RETURN some_row; END; ' LANGUAGE 'plpgsql' VOLATILE; Oh well, I'm glad I tested the approach out before going too far down this road. Thanks again for your timely help. Richard -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] polymorphic function in 7.4 vs. 8.3
Richard Rosenberg wrote: On Thursday 11 June 2009 14:49:46 Tom Lane wrote: Sure you can't move the DB off 7.4? There would be pretty considerable benefits from adopting some recent release instead. regards, tom lane Don't I know it. I am SOL as the machine is hosted/shared out by an external provider. I can do it by getting rid of the polymorphism - breaking the columns into separate args - as you say: Hmm. Some subliminal coercion seems in order here. "Gee I hope it doesn't leek out that is so poorly run that they're stuck on postgres 7.4 with no hope of ever catching up with the rest of the world" -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
