Re: [SQL] list variable attributes in one select
am Mon, dem 22.01.2007, um 8:43:48 +0100 mailte Louis-David Mitterrand folgendes: > "person_type" contains differents caracteristics for a person (actor, > director, author, etc.) who can have several types, hence the need for > the person_to_type table. > > I'd like to know if I can list in one SELECT command a person and all of > its types, given that the number of types can be 0 to n. > > For example, for a given person I'd like to obtain: > > "John Doe", "actor", "playright", "author" > > or > > "Jane Doe", "director" > > in one select. Yes, imagine, you have 2 tables: test=*# select * from f1; id | name +-- 1 | foo 2 | bar 3 | batz (3 rows) test=*# select * from f2; i_id | f1_id --+--- 1 | 1 1 | 2 2 | 1 2 | 2 2 | 3 (5 rows) f1 contains your characteristics, f2 contains the assignment person -> characteristics. Now you can see all characteristics for person with id=1: test=*# select array_to_string(array(select name from f1 where id in (select f1_id from f2 where i_id = 1)), ', '); array_to_string - bar, foo (1 row) Hope that helps, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
FW: [SQL] Possible to emulate pre-8.2 behaviour of SET CONSTRAINTS?
That sounds like a plan - will give it a go. Thanks! simon -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, January 22, 2007 3:37 AM To: Simon Kinsella Cc: [email protected] Subject: Re: [SQL] Possible to emulate pre-8.2 behaviour of SET CONSTRAINTS? "Simon Kinsella" <[EMAIL PROTECTED]> writes: > My system currently runs on PostgreSQL 8.1 and makes use of the old > behaviour of SET CONSTRAINTS, namely that the command is applied to > all constraints that match the specified name. Unfortunately that was pretty far away from what the SQL spec says :-( > This makes it very easy to write > a general-case function that can change the DEFERRED mode on a given > constraint that is present in several similar schemas (sounds odd > maybe but it works very well in my case!). I think you could do it fairly easily still, eg for rec in select nspname from pg_namespace n join pg_constraint c on n.oid = c.connamespace where conname = $1 loop execute 'set constraints ' || quote_ident(rec.nspname) || '.' || quote_ident($1) || ' immediate'; end loop; Exceedingly untested, but something close to this seems like it'd solve your problem. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Schema sample
Hi list, I would like to ask you if there is any schema that someone can send me. I must make some Replication testes and would like to use a simple schema with two or three tables would be nice. My best regards... -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/
Re: [SQL] Schema sample
Dnia Mon, 22 Jan 2007 17:38:09 -0200, Ezequias Rodrigues da Rocha napisał(a): > Hi list, > > I would like to ask you if there is any schema that someone can send me. I > must make some Replication testes and would like to use a simple schema > with two or three tables would be nice. You can find some sample databases here: http://pgfoundry.org/frs/?group_id=1000150 -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org |So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: [EMAIL PROTECTED] *---' http://www.naszedzieci.org ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Schema sample
On 1/22/07, Marcin Stępnicki <[EMAIL PROTECTED]> wrote: Dnia Mon, 22 Jan 2007 17:38:09 -0200, Ezequias Rodrigues da Rocha napisał(a): > Hi list, > > I would like to ask you if there is any schema that someone can send me. I > must make some Replication testes and would like to use a simple schema > with two or three tables would be nice. You can find some sample databases here: http://pgfoundry.org/frs/?group_id=1000150 Hey, great idea. I think I will use this for unit testing our generic code libraries since they are supposed to be independent of any particular application we build. -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
[SQL] How to query by column names
This may be a simple but can I create a query such as select (select attname from pg_attribute where attrelid = (select relfilenode from pg_class where relname = 't1') and attisdropped = false and attnum > 0) from t1; I get ERROR: more than one row returned by a subquery used as an expression Thanks Richard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to query by column names
I think this is what you're looking for Richard: SELECT attname FROM pg_attribute pa, pg_class pc WHERE pc.relname = 't1' AND pa.attrelid = pc.relfilenode AND pa.attisdropped IS FALSE AND pa.attnum > 0; Let me know if it doesn't do what you intended. On Mon, 22 Jan 2007, Richard Ray wrote: This may be a simple but can I create a query such as select (select attname from pg_attribute where attrelid = (select relfilenode from pg_class where relname = 't1') and attisdropped = false and attnum > 0) from t1; I get ERROR: more than one row returned by a subquery used as an expression Thanks Richard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to query by column names
This is not exactly what I need I want to return the data in t1 On Mon, 22 Jan 2007, Jeff Frost wrote: I think this is what you're looking for Richard: SELECT attname FROM pg_attribute pa, pg_class pc WHERE pc.relname = 't1' AND pa.attrelid = pc.relfilenode AND pa.attisdropped IS FALSE AND pa.attnum > 0; Let me know if it doesn't do what you intended. On Mon, 22 Jan 2007, Richard Ray wrote: This may be a simple but can I create a query such as select (select attname from pg_attribute where attrelid = (select relfilenode from pg_class where relname = 't1') and attisdropped = false and attnum > 0) from t1; I get ERROR: more than one row returned by a subquery used as an expression Thanks Richard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to query by column names
Perhaps I should have asked this earlier. What information are you trying to extract? On Mon, 22 Jan 2007, Richard Ray wrote: This is not exactly what I need I want to return the data in t1 On Mon, 22 Jan 2007, Jeff Frost wrote: I think this is what you're looking for Richard: SELECT attname FROM pg_attribute pa, pg_class pc WHERE pc.relname = 't1' AND pa.attrelid = pc.relfilenode AND pa.attisdropped IS FALSE AND pa.attnum > 0; Let me know if it doesn't do what you intended. On Mon, 22 Jan 2007, Richard Ray wrote: This may be a simple but can I create a query such as select (select attname from pg_attribute where attrelid = (select relfilenode from pg_class where relname = 't1') and attisdropped = false and attnum > 0) from t1; I get ERROR: more than one row returned by a subquery used as an expression Thanks Richard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to query by column names
All attributes of t1 Where (select attname from pg_attribute where attrelid = (select relfilenode from pg_class where relname = 't1') and attisdropped = false and attnum > 0) is a substitute for * On Mon, 22 Jan 2007, Jeff Frost wrote: Perhaps I should have asked this earlier. What information are you trying to extract? On Mon, 22 Jan 2007, Richard Ray wrote: This is not exactly what I need I want to return the data in t1 On Mon, 22 Jan 2007, Jeff Frost wrote: I think this is what you're looking for Richard: SELECT attname FROM pg_attribute pa, pg_class pc WHERE pc.relname = 't1' AND pa.attrelid = pc.relfilenode AND pa.attisdropped IS FALSE AND pa.attnum > 0; Let me know if it doesn't do what you intended. On Mon, 22 Jan 2007, Richard Ray wrote: This may be a simple but can I create a query such as select (select attname from pg_attribute where attrelid = (select relfilenode from pg_class where relname = 't1') and attisdropped = false and attnum > 0) from t1; I get ERROR: more than one row returned by a subquery used as an expression Thanks Richard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] How to query by column names
So why are you avoiding "SELECT * FROM t1;" ? You'd probably also be happier using information_schema to get the column names. On Mon, 22 Jan 2007, Richard Ray wrote: All attributes of t1 Where (select attname from pg_attribute where attrelid = (select relfilenode from pg_class where relname = 't1') and attisdropped = false and attnum > 0) is a substitute for * On Mon, 22 Jan 2007, Jeff Frost wrote: Perhaps I should have asked this earlier. What information are you trying to extract? On Mon, 22 Jan 2007, Richard Ray wrote: This is not exactly what I need I want to return the data in t1 On Mon, 22 Jan 2007, Jeff Frost wrote: I think this is what you're looking for Richard: SELECT attname FROM pg_attribute pa, pg_class pc WHERE pc.relname = 't1' AND pa.attrelid = pc.relfilenode AND pa.attisdropped IS FALSE AND pa.attnum > 0; Let me know if it doesn't do what you intended. On Mon, 22 Jan 2007, Richard Ray wrote: This may be a simple but can I create a query such as select (select attname from pg_attribute where attrelid = (select relfilenode from pg_class where relname = 't1') and attisdropped = false and attnum > 0) from t1; I get ERROR: more than one row returned by a subquery used as an expression Thanks Richard ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to query by column names
On Mon, 22 Jan 2007, Jeff Frost wrote: So why are you avoiding "SELECT * FROM t1;" ? I was affeared that if I brought my total ignorance to light I would be band from the list but here goes. I work in UNIX/Linux environments. It's my habit to record my scripts. A simple example: #!/bin/bash CMD="psql -d test \"select * from t1\"" echo $CMD >> my_log eval $CMD | while read x; do do_something_with_x done In this example * expands to all files in the current working directory. I was attempting to get around this by enumerating the table attributes. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to query by column names
On Mon, 22 Jan 2007, Richard Ray wrote: On Mon, 22 Jan 2007, Jeff Frost wrote: So why are you avoiding "SELECT * FROM t1;" ? I was affeared that if I brought my total ignorance to light I would be band from the list but here goes. I work in UNIX/Linux environments. It's my habit to record my scripts. A simple example: #!/bin/bash CMD="psql -d test \"select * from t1\"" echo $CMD >> my_log eval $CMD | while read x; do do_something_with_x done In this example * expands to all files in the current working directory. I was attempting to get around this by enumerating the table attributes. Oh! Why didn't you just say that in the first place. You just need quotes. Try this: psql -c 'select * from t1' test or psql -c "select * from t1" test or echo "select * from t1" | psql test -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to query by column names
On Tue, 23 Jan 2007, Josh Williams wrote: From: Jeff Frost <[EMAIL PROTECTED]> On Mon, 22 Jan 2007, Richard Ray wrote: ... That's definitely part of it. I'm assuming the above is an abridged example and the OP is doing something dynamic with the query. The real trouble is Bash likes to expand the asterisk into a list of every file in the current directory when you try to push the command through a variable. So it's just a matter of finding a way to escape the * character to keep Bash from globbing, which unfortunately right now is escaping me (no pun intended.) Oh...you just need to put quotes around the variable like this: #!/bin/bash CMD="psql -c 'select * from products;' jefftest" echo "$CMD" >> my_log eval "$CMD" | while read x; do echo $x done discord:~ $ /tmp/test.sh productid | name | price ---+---+--- 1 | Notepad | 1.99 3 | Legal Pad | 2.99 (2 rows) discord:~ $ cat my_log psql -c 'select * from products;' jefftest At any rate, if that's still a problem, you can turn off globbing, do what needs doing, then turn on globbing like the following: #!/bin/bash # # Turn off globbing # set -o noglob # ... # Do your * laden work here # ... # Turn on globbing set +o noglob Two reasonable workarounds come to mind: 1. Turn off Bash's pathname expansion: #!/bin/bash -f This will of course disable it script-wide, and thus will break any place you actually are trying to use this feature, if at all. 2. Don't put an * in the variable. If all you're really doing is replacing the table name then only stick that into a variable, say tablename, and directly execute the rest: psql -d test -c "SELECT * FROM $tablename" | while etc Worst case, you'll end up with a messy $leftside and $rightside variable set. To answer the original question, the field must be hard coded either as a list or that perhaps over-used(?) asterisk. If you really need to pull and use that from the table definition you'll need two round trips to the server. -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] How to query by column names
From: Jeff Frost <[EMAIL PROTECTED]> > On Mon, 22 Jan 2007, Richard Ray wrote: ... > > #!/bin/bash > > CMD="psql -d test \"select * from t1\"" > > echo $CMD >> my_log > > eval $CMD | > > while read x; do > > do_something_with_x > > done > > > > In this example * expands to all files in the current working directory. > > I was attempting to get around this by enumerating the table attributes. > > Oh! Why didn't you just say that in the first place. You just need quotes. ... That's definitely part of it. I'm assuming the above is an abridged example and the OP is doing something dynamic with the query. The real trouble is Bash likes to expand the asterisk into a list of every file in the current directory when you try to push the command through a variable. So it's just a matter of finding a way to escape the * character to keep Bash from globbing, which unfortunately right now is escaping me (no pun intended.) Two reasonable workarounds come to mind: 1. Turn off Bash's pathname expansion: #!/bin/bash -f This will of course disable it script-wide, and thus will break any place you actually are trying to use this feature, if at all. 2. Don't put an * in the variable. If all you're really doing is replacing the table name then only stick that into a variable, say tablename, and directly execute the rest: psql -d test -c "SELECT * FROM $tablename" | while etc Worst case, you'll end up with a messy $leftside and $rightside variable set. To answer the original question, the field must be hard coded either as a list or that perhaps over-used(?) asterisk. If you really need to pull and use that from the table definition you'll need two round trips to the server. Best of luck, - Josh Williams ---(end of broadcast)--- TIP 6: explain analyze is your friend
