[SQL] Copy Views From Database?
Hi Guys, I'm using PostGreSQL with a Java project since I'm a proffesional Java developer. I've a database that have about 120 views in PostGreSQL 7.0 that I need to copy into PostGreSQL 8.0. I can copy them one-by-one in pgAdmin but I don't have the time! Is there a maybe a tool that I can use to copy views as-is from one database into another? Kind Regards, Lennie De Villiers Java Developer CorePat Systems (Pty) Ltd www.corepat.com mail2web - Check your email from the web at http://mail2web.com/ . ---(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
[SQL] cancel <[EMAIL PROTECTED]>
This message was cancelled from within Mozilla. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Copy Views From Database?
use pg_dump and pg_restore :) On 22.09.2005 10:37, [EMAIL PROTECTED] wrote: Hi Guys, I'm using PostGreSQL with a Java project since I'm a proffesional Java developer. I've a database that have about 120 views in PostGreSQL 7.0 that I need to copy into PostGreSQL 8.0. I can copy them one-by-one in pgAdmin but I don't have the time! Is there a maybe a tool that I can use to copy views as-is from one database into another? Kind Regards, Lennie De Villiers Java Developer CorePat Systems (Pty) Ltd www.corepat.com mail2web - Check your email from the web at http://mail2web.com/ . ---(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 6: explain analyze is your friend
Re: [SQL] Copy Views From Database?
On 22.09.2005 10:37, [EMAIL PROTECTED] wrote: Is there a maybe a tool that I can use to copy views as-is from one database into another? pg_dump and pg_restore :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Updating cidr column with network operator
Hi SQLers, I have a fk from address to network and try to update the foreign key column to point at the network, "it belongs to": CREATE TABLE network ( id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address' ) CREATE TABLE address ( id inet PRIMARY KEY , -- 'PK of IPv4/6 host address' network cidr NOT NULL -- 'FK to Network table' REFERENCES network ON DELETE CASCADE ON UPDATE CASCADE ) I tried (using the WHERE clause to eliminate the addresses were no corresponding net exists): UPDATE address SET network = (SELECT N.id WHERE A.id << N.id) FROM address A, network N WHERE A.id << N.id; But this ended up with all network columns pointing at the same net (-:). Any help would be appreciated. Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 ---(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] Copy Views From Database?
On 22.09.2005 11:47, [EMAIL PROTECTED] wrote: How would you use it to only dump / restore the views? I don't want any database tables. Please RTFM. But because I'm such a nice guy: Create a binary dump of the schema: pg_dump -v -s -i -F c -Z 9 -U -f /tmp/dump.bin Use the -l option of pg_restore to create a TOC file and filter your views with grep: pg_restore -l /tmp/dump.bin | grep -E "[[:space:]]VIEW[[:space:]]" > /tmp/tmp.toc Check your TOC file with less to see if everything you want is in it: less /tmp/tmp.toc In case all seems fine run pg_restore with that TOC list as argument and check the SQL statements it generates: pg_restore -i -v -O -L /tmp/tmp.toc /tmp/dump.bin | less In case all seems fine again, run it against your other database: pg_restore -i -v -O -d -U -L /tmp/tmp.toc /tmp/dump.bin Hope it helps, in case it trashes your server, don't blame me and read the manual before you're doing anything. Best regards, Hannes Dorbath ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] postgres on the comman line
Hello, I like to automate a daily check and like to run select statement via cron but this seems to be more tricky than I thought I tried the following: su postgres -c "select count(*) from TABLE where xx;" I have the probelm that I am allways asked for the password - I did not find a way how to pass the password. Also I like to direct the result to a file, I assume I can do this via > but not haveing passed the first problem I did not check this. I am happy for every hint Thanks a lot Michael ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] postgres on the comman line
Hi Michael, On Thursday 22 September 2005 15:55, Michael Höller wrote: | I like to automate a daily check and like to run select statement via | cron but this seems to be more tricky than I thought | | I tried the following: | su postgres -c "select count(*) from TABLE where xx;" you mean su - postgres -c "echo 'select count(*) from TABLE where xx;'|psql" ? | I have the probelm that I am allways asked for the password - I did not | find a way how to pass the password. search the documentation for the .pgpass file. It should contain lines with colon separated values consisting of host:port:database:user:password and must not be world-readable. | Also I like to direct the result to a file, I assume I can do this via > | but not haveing passed the first problem I did not check this. yes. Ciao, Thomas -- Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] postgres on the comman line
am 22.09.2005, um 15:55:53 +0200 mailte "Michael Höller" folgendes: > > > Hello, > > I like to automate a daily check and like to run select statement via > cron but this seems to be more tricky than I thought > > I tried the following: > su postgres -c "select count(*) from TABLE where xx;" - run the cron direcly as postgres - echo "select foo from bar" | psql ... or: write your sql-query i a file and "psql -f file" > > I have the probelm that I am allways asked for the password - I did not > find a way how to pass the password. put it in the ~/.psqlrc > > Also I like to direct the result to a file, I assume I can do this via > yes, this is possible. Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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] postgres on the comman line
O "Michael Hφller" έγραψε στις Sep 22, 2005 : > > > Hello, > > I like to automate a daily check and like to run select statement via > cron but this seems to be more tricky than I thought > > I tried the following: > su postgres -c "select count(*) from TABLE where xx;" Firstoff, 'select' is nor a unix command, neither a postgresql program. So you need something like: su postgres -c "psql -c 'select count(*) from TABLE where xx'" If you are the only one with an account on the machine then you might try to tweak ~postgres/data/pg_hba.conf and set method 'trust' for local access (no tcpip,psql without the -h option) . Also you could set env variables PGPASSWORD, PGUSER. > > I have the probelm that I am allways asked for the password - I did not > find a way how to pass the password. > > Also I like to direct the result to a file, I assume I can do this via > > but not haveing passed the first problem I did not check this. > > I am happy for every hint > > Thanks a lot > Michael > > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- -Achilleus ---(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] Updating cidr column with network operator
Axel Rau wrote: Hi SQLers, I have a fk from address to network and try to update the foreign key column to point at the network, "it belongs to": CREATE TABLE network ( id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address' ) CREATE TABLE address ( id inet PRIMARY KEY , -- 'PK of IPv4/6 host address' network cidr NOT NULL-- 'FK to Network table' REFERENCES network ON DELETE CASCADE ON UPDATE CASCADE ) I tried (using the WHERE clause to eliminate the addresses were no corresponding net exists): But you can't insert a row in address w/o a valid network.id? That's what the fk ensures. Perhaps you could elaborate more? Are you trying to *put* on the fk and you currently have bad data? UPDATE address SET network = (SELECT N.id WHERE A.id << N.id) FROM address A, network N WHERE A.id << N.id; This also makes no sense. For starters, << is "bitwise shift left" ... But this ended up with all network columns pointing at the same net (-:). Any help would be appreciated. Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 ---(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 -- Daryl "We want great men who, when fortune frowns, will not be discouraged." -- Colonel Henry Knox, 1776 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Using descriptor areas to insert
I'm in the final stages of porting a large body of ESQL software from Informix to PostgreSQL (V8). The last few Informix-based functions involve Informix descriptor areas to insert rows into a table. I've sorted out the logic for using PostgreSQL descriptor areas for select statements. How do you go about using the PostgreSQL descriptor areas for insert statements? Is there another way to approach the problem of insert records into an arbitrary table using dynamic SQL? Thanks ... -- Andrew Rost National Operational Hydrologic Remote Sensing Center (NOHRSC) National Weather Service, NOAA 1735 Lake Dr. West, Chanhassen, MN 55317-8582 Voice: (952)361-6610 x 234 Fax: (952)361-6634 [EMAIL PROTECTED] http://www.nohrsc.nws.gov ---(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
[SQL] Help with a view
I've created a view 'principals' that gives me this output: pgslekt=> select * from principals where event=15821; person | event | place | sort_date | tag_type +---+---++-- 2 | 15821 | 1152 | 1999-09-17 |4 3 | 15821 | 1152 | 1999-09-17 |4 (2 rows) How do I go about selecting the "other" person of this event, given that I have one of them and the event number? I'd also like a view that gives me the two rows combined into one, with "person" replaced by p1 and p2. -- Leif Biberg Kristensen http://solumslekt.org/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Updating cidr column with network operator
Thank you for responding, Daryl, Am 22.09.2005 um 16:45 schrieb Daryl Richter: Axel Rau wrote: Hi SQLers, I have a fk from address to network and try to update the foreign key column to point at the network, "it belongs to": CREATE TABLE network ( id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address' ) CREATE TABLE address ( id inet PRIMARY KEY , -- 'PK of IPv4/6 host address' network cidr NOT NULL-- 'FK to Network table' REFERENCES network ON DELETE CASCADE ON UPDATE CASCADE ) I tried (using the WHERE clause to eliminate the addresses were no corresponding net exists): But you can't insert a row in address w/o a valid network.id? That's what the fk ensures. Perhaps you could elaborate more? Are you trying to *put* on the fk and you currently have bad data? The fk requires a corresponding row in network. But my update tries to reference the right network, that one where the ip address belongs to. UPDATE address SET network = (SELECT N.id WHERE A.id << N.id) FROM address A, network N WHERE A.id << N.id; This also makes no sense. For starters, << is "bitwise shift left" ... I'm using 8.0.3 and there are some new operators related to inet and cidr data types. On page 157, I found "<<" as address/network "is contained in" network. Finding the net where an address belongs to works as: SELECT id FROM network WHERE inet '$p_ipSource' << id; Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Help with a view
SELECT * FROM principals WHERE event = 15821 AND person != 2? Dmitri > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Leif B. > Kristensen > Sent: Thursday, September 22, 2005 1:30 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Help with a view > > > I've created a view 'principals' that gives me this output: > > pgslekt=> select * from principals where event=15821; > person | event | place | sort_date | tag_type > +---+---++-- > 2 | 15821 | 1152 | 1999-09-17 |4 > 3 | 15821 | 1152 | 1999-09-17 |4 > (2 rows) > > How do I go about selecting the "other" person of this event, > given that > I have one of them and the event number? > > I'd also like a view that gives me the two rows combined into > one, with > "person" replaced by p1 and p2. > -- > Leif Biberg Kristensen > http://solumslekt.org/ > > ---(end of > broadcast)--- > TIP 4: Have you searched our list archives? > http://archives.postgresql.org The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with a view
On Thursday 22 September 2005 20:03, Dmitri Bichko wrote:
> SELECT * FROM principals WHERE event = 15821 AND person != 2?
Sure, that's a concise answer to what I actually wrote, but it wasn't
exactly what I intended :)
Basically, what I've got is the first person and the tag_type. I can do
it with a function from PHP:
function get_spouses($p) {
$handle = pg_query("select person from principals
where event in (select event from principals
where person = $p and tag_type = " .MARR. ")
order by sort_date");
$i=0;
$spouses = array();
while ($row = pg_fetch_row($handle) {
if $row[0] != $p
$spouses[$i++] = $row[0];
}
return $spouses;
}
But this is *ugly* ...
--
Leif Biberg Kristensen
http://solumslekt.org/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Updating cidr column with network operator
Axel Rau wrote: Thank you for responding, Daryl, Am 22.09.2005 um 16:45 schrieb Daryl Richter: Axel Rau wrote: Hi SQLers, I have a fk from address to network and try to update the foreign key column to point at the network, "it belongs to": CREATE TABLE network ( id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address' ) CREATE TABLE address ( id inet PRIMARY KEY , -- 'PK of IPv4/6 host address' network cidr NOT NULL-- 'FK to Network table' REFERENCES network ON DELETE CASCADE ON UPDATE CASCADE ) I tried (using the WHERE clause to eliminate the addresses were no corresponding net exists): But you can't insert a row in address w/o a valid network.id? That's what the fk ensures. Perhaps you could elaborate more? Are you trying to *put* on the fk and you currently have bad data? The fk requires a corresponding row in network. But my update tries to reference the right network, that one where the ip address belongs to. I'm still not understanding what you're trying to do, perhaps its a language issue. :) Let me try again. I built your schema and inserted some rows: insert into network( id ) values( '10.1' ); insert into address( id, network ) values( '10.1.0.1', '10.1' ); insert into address( id, network ) values( '10.1.0.2', '10.1' ); insert into address( id, network ) values( '10.1.0.3', '10.1' ); I then select from network: id --- 10.1.0.0/16 and from address: idnetwork --- 10.1.0.1 10.1.0.0/16 10.1.0.2 10.1.0.0/16 10.1.0.3 10.1.0.0/16 Why do you now want to update address.network? They are already pointing to the right network, aren't they? I think if you provide some sample data we can figure this out. UPDATE address SET network = (SELECT N.id WHERE A.id << N.id) FROM address A, network N WHERE A.id << N.id; This also makes no sense. For starters, << is "bitwise shift left" ... I'm using 8.0.3 and there are some new operators related to inet and cidr data types. On page 157, I found "<<" as address/network "is contained in" network. Finding the net where an address belongs to works as: SELECT id FROM network WHERE inet '$p_ipSource' << id; Ahh, ok. see above. Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Daryl ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Issue with UPDATE statement on v8
Hello, I'm sorry if this has been sent to the community multiple times. I am not able to determine whether my posts have gotten through. If you have rec'd this multiple times, please let me know. We have recently migrated to Postgres 8 (not sure of exactly which build). We have noticed that a few functions that were working previously are no longer behaving as expected. One function in particular is giving me a strange result. The function giving us the problem is much more complicated, but for simplicity I've included one that is easier to read and results in the same behavior. UPDATE t_summary SETavailability = 7 WHERE oid = 28245084 When this query is executed (within a function or without) the database will simply hang. If the UPDATE is turned into a SELECT, the query works just fine. For some reason, the UPDATE is just not working. This same function/query works fines in Postgres 7.2. The schema for the targeted table is shown below. CREATE TABLE t_summary ( id varchar(20) NULL, availability int4 NULL DEFAULT 0, ) Has anyone else experienced a similar issue? If more information is needed to determine the problem, please let me know. I've trimmed down the function's query and table's schema for this posting. Thanks in advance! kh This message is intended only for the use of the individual(s) or entity to which it is addressed and may contain information that is privileged, confidential, and/or proprietary to RealPage and its affiliated companies. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, forwarding or copying of this communication is prohibited without the express permission of the sender. If you have received this communication in error, please notify the sender immediately and delete the original message. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Help with a view
> Basically, what I've got is the first person and the tag_type. I can do
> it with a function from PHP:
>
> function get_spouses($p) {
> $handle = pg_query("select person from principals
>where event in (select event from principals
>where person = $p and tag_type = " .MARR. ")
>order by sort_date");
What about adding an extra condition ' and person <> $p ?
select person from principals as a
where event in (select event from principals as b
where person = $p and tag_type = " .MARR. ")
and a.person <> $p
order by sort_date
>pgslekt=> select * from principals where event=15821;
>person | event | place | sort_date | tag_type
>+---+---++--
> 2 | 15821 | 1152 | 1999-09-17 |4
> 3 | 15821 | 1152 | 1999-09-17 |4
(2 rows)
>I'd also like a view that gives me the two rows combined into one, with
>"person" replaced by p1 and p2.
might consider grouping by all other columns except person and use an
aggregate function over person ,
CREATE VIEW blah AS select list(person) as persons , event , place ,
sort_date , tag_type from principals group by event , place ,
sort_date , tag_type ;
(list is a custom function in my database , currently not posted)
not sure though if i got your problem exactly.
regds
mallah.
> $i=0;
> $spouses = array();
> while ($row = pg_fetch_row($handle) {
> if $row[0] != $p
> $spouses[$i++] = $row[0];
> }
> return $spouses;
> }
---(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] Issue with UPDATE statement on v8
"Kenneth Hutchinson" <[EMAIL PROTECTED]> writes: > UPDATE t_summary > SETavailability = 7 > WHERE oid = 28245084 > When this query is executed (within a function or without) the database > will simply hang. Is it really hung, or just taking an awfully long time? If the backend is consuming no CPU or I/O then I'd agree it's the former; please look into the pg_locks view to see if you can find out what it's waiting for. If it's the latter, maybe you neglected to create an index on OID? > Has anyone else experienced a similar issue? We'd certainly have heard about it if so. But you haven't provided enough info to let anyone reproduce the problem for investigation. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Issue with UPDATE statement on v8
On 9/23/05, Kenneth Hutchinson <[EMAIL PROTECTED]> wrote: > Hello, > > I'm sorry if this has been sent to the community multiple times. I am > not able to determine whether my posts have gotten through. If you have > rec'd this multiple times, please let me know. > > We have recently migrated to Postgres 8 (not sure of exactly which > build). We have noticed that a few functions that were working > previously are no longer behaving as expected. One function in > particular is giving me a strange result. > > The function giving us the problem is much more complicated, but for > simplicity I've included one that is easier to read and results in the > same behavior. > > UPDATE t_summary > SETavailability = 7 > WHERE oid = 28245084 > > When this query is executed (within a function or without) the database > will simply hang. see if the update statement is being blocked by some other statement by running following SQL stmt from another session while the first session is hung SELECT h.pid AS blocker, w.pid AS blockee FROM ONLY pg_locks h, ONLY pg_locks w WHERE h.granted AND NOT w.granted AND (h.relation = w.relation AND h."database" = w."database" OR h."transaction" = w."transaction"); (sql above was posted by mr. Tom Lane in a particular reply) But You have to enable command string in statictics part of postgresql.conf file to know which pid corresponds to which sql. http://www.postgresql.org/docs/8.0/interactive/monitoring-stats.html#MONITORING-STATS-SETUP Hope it helps regds mallah. If the UPDATE is turned into a SELECT, the query > works just fine. For some reason, the UPDATE is just not working. This > same function/query works fines in Postgres 7.2. > > The schema for the targeted table is shown below. > > CREATE TABLE t_summary ( > id varchar(20) NULL, > availability int4 NULL DEFAULT 0, > ) > > Has anyone else experienced a similar issue? If more information is > needed to determine the problem, please let me know. I've trimmed down > the function's query and table's schema for this posting. > > Thanks in advance! > > kh > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with a view
I don't understand how this is different:
SELECT person FROM principals
WHERE event IN (SELECT event FROM principals WHERE person = $p AND
tag_type = " .MARR. ")
AND person != $p
ORDER BY sort_date
Or without subselects:
SELECT p1.person
FROM principals p1
JOIN principals p2 USING(event)
WHERE p2 person = $p
AND p2.tag_type = " .MARR. "
AND p1.person != $p
ORDER BY sort_date
Am I being confused?
Dmitri
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Leif B.
> Kristensen
> Sent: Thursday, September 22, 2005 2:12 PM
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Help with a view
>
>
> On Thursday 22 September 2005 20:03, Dmitri Bichko wrote:
>
> > SELECT * FROM principals WHERE event = 15821 AND person != 2?
>
> Sure, that's a concise answer to what I actually wrote, but it wasn't
> exactly what I intended :)
>
> Basically, what I've got is the first person and the
> tag_type. I can do
> it with a function from PHP:
>
> function get_spouses($p) {
> $handle = pg_query("select person from principals
>where event in (select event from principals
>where person = $p and tag_type = " .MARR. ")
>order by sort_date");
> $i=0;
> $spouses = array();
> while ($row = pg_fetch_row($handle) {
> if $row[0] != $p
> $spouses[$i++] = $row[0];
> }
> return $spouses;
> }
>
> But this is *ugly* ...
> --
> Leif Biberg Kristensen
> http://solumslekt.org/
>
> ---(end of
> broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
The information transmitted is intended only for the person or entity to which
it is addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any action
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. If you received this in error, please contact
the sender and delete the material from any computer
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] Using descriptor areas to insert
Andrew, > I'm in the final stages of porting a large body of ESQL software from > Informix to PostgreSQL (V8). The last few Informix-based functions > involve Informix descriptor areas to insert rows into a table. I've > sorted out the logic for using PostgreSQL descriptor areas for select > statements. How do you go about using the PostgreSQL descriptor areas > for insert statements? Is there another way to approach the problem of > insert records into an arbitrary table using dynamic SQL? Hmmm, I don't quite follow you, mostly because I'm *not* an Informix person. Could you give an example? Also, if you still have contact, Dave Cramer or Elein should be able to answer this question ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Primary and Foreign Key?
I have the following related tables: PEOPLE -- peopleid pkey, name, etc GROUPS - groupid pkey, description, etc PEOPLEGROUPS --- peopleid pkey/fkey, groupid pkey/fkey What is the CORRECT way (in Postgres) to define the PEOPLEGROUPS table so that it has both the double primary key AND still acts as a foreign key for people.peopleid and groups.groupid? Can i specify both or is this not necessary? Any suggestions would be appreciated. Thanks! -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.4/109 - Release Date: 9/21/2005 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Primary and Foreign Key?
This is valid ddl to accomplish what you wish.
create table peoplegroups {
peopleid int not null,
groupid int not null,
primary key (peopleid, groupid),
foreign key (peopleid) references people,
foreign key (groupid) references group
}
Check the docs for other options etc.
From: "Announce" <[EMAIL PROTECTED]>
To: "PostgreSQL SQL List"
Subject: [SQL] Primary and Foreign Key?
Date: Thu, 22 Sep 2005 20:13:35 -0500
I have the following related tables:
PEOPLE
--
peopleid pkey,
name,
etc
GROUPS
-
groupid pkey,
description,
etc
PEOPLEGROUPS
---
peopleid pkey/fkey,
groupid pkey/fkey
What is the CORRECT way (in Postgres) to define the PEOPLEGROUPS table so
that it has both the double primary key AND still acts as a foreign key for
people.peopleid and groups.groupid? Can i specify both or is this not
necessary? Any suggestions would be appreciated.
Thanks!
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.4/109 - Release Date: 9/21/2005
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] Functions, transactions and RETURN
After reading up on Postgres documentation, it seems that transactions and savepoints are not available to functions, and savepoints are implemented via BEGIN.. EXCEPTION.. END blocks. I have a function returning an int4 with the following proposed structure: -- BEGIN -- Start the transaction, lock tables SAVEPOINT start; LOCK TABLE backend.table IN ACCESS EXCLUSIVE MODE; LOCK TABLE backend.table2 IN SHARE MODE; -- Check data in another table. IF NOT tt_check_table2(var1) THEN ROLLBACK TO SAVEPOINT start; RETURN -1; -- E_NO_ACCESS END IF; -- Check data in this table. IF tt_check_table(var2) THEN ROLLBACK TO SAVEPOINT start; RETURN -2000; -- E_DUP_COURSE END IF; -- -- Insert the row BEGIN INSERT INTO backend.table (foo, bar, baz) VALUES (1, 2, 3); -- Success RETURN 0; EXCEPTION WHEN OTHERS THEN ROLLBACK TO SAVEPOINT start; RETURN -32767; -- E_UNKNOWN END; ROLLBACK TO SAVEPOINT start; END; -- I can't see how to implement both savepoints and returning distinct values with the BEGIN.. RAISE EXCEPTION.. EXCEPTION.. END method proposed in previous mailing list posts. Are there any suggestions on how to implement this? Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Difficulties with a master-detail query
Hi, list! You have most probably met the same problem: I have a master table, describing the objecs I'm interested in - let's say employees. I have a details table, defining all possible values of one of the properties of the objects - let's say languages spoken. And of course I have a table desribing the table the connection between the latter two - N:N (fairly standard solution). Here is the scheme: CREATE TABLE employee ( employee_id serial PRIMARY KEY, name varchar(20) ); CREATE TABLE skill ( skill_id serial PRIMARY KEY, name varchar(20) ); CREATE TABLE employee_skill ( employee_id integer, skill_id integer, CONSTRAINT employee_skill_employee_id_fkey FOREIGN KEY (employee_id) REFERENCES employee(employee_id), CONSTRAINT employee_skill_skill_id_fkey FOREIGN KEY (skill_id) REFERENCES skill (skill_id), CONSTRAINT employee_skill_employee_id_key UNIQUE (employee_id, skill_id) ); I would like to get all employees, who speak two specified languages (say german and french). The following query gives me that, bu I don't like it (see for yourself): SELECT е.employee_id, е.name, COUNT(s.skill_id) FROM employee AS e INNER JOIN employee_skill AS es ON e.employee_id=es.employee_id INNER JOIN skill AS s ON s.skill_id=es.skill_id AND s.skill_id IN (1, 2) GROUP BY e.employee_id, e.name HAVING COUNT(s.skill_id)>=2; Here "(1, 2)" are the IDs for those predefined two languages, got from the "skill" table. Аnd that two in "COUNT(s.skill_id)>=2" is there because the count of the languages. Any ideas for simpler and more universal query? Please CC me, because I'm not subscribed. -- Milen A. Radev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Issue with UPDATE statement on v8
Hello, We have recently migrated to Postgres 8 (not sure of exactly which build). We have noticed that a few functions that were working previously are no longer behaving as expected. One function in particular is giving me a strange result. The function giving us the problem is much more complicated, but for simplicity I‘ve included one that is easier to read and results in the same behavior. UPDATE t_summary SET availability = 7 WHERE oid = 28245084 When this query is executed (within a function or without) the database will simply hang. If the UPDATE is turned into a SELECT, the query works just fine. For some reason, the UPDATE is just not working. This same function/query works fines in Postgres 7.2. The schema for the targeted table is shown below. CREATE TABLE t_summary ( id varchar(20) NULL, availability int4 NULL DEFAULT 0, ) Has anyone else experienced a similar issue? If more information is needed to determine the problem, please let me know. I’ve trimmed down the function’s query and table’s schema for this posting. Thanks in advance! kh This message is intended only for the use of the individual(s) or entity to which it is addressed and may contain information that is privileged, confidential, and/or proprietary to RealPage and its affiliated companies. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, forwarding or copying of this communication is prohibited without the express permission of the sender. If you have received this communication in error, please notify the sender immediately and delete the original message.
Re: [SQL] Functions, transactions and RETURN
Hate to reply to my own posts, but I thought the solution I've come across may help others with problems implementing savepoints and transactions in functions. This function implements rollbacks whilst still returning a valid row instead of an exception. A temporary variable is used to get around the ugly lack of SAVEPOINTs. -- CREATE OR REPLACE FUNCTION tr_addcoursearea(employeeno, coursearea) RETURNS int4 AS $BODY$DECLARE transid int4; errcode int4; BEGIN -- Setup default return code. This is used if we hit an -- exception that we didn't throw. SELECT -32767 into errcode; -- E_UNKNOWN LOCK TABLE backend.courseareas IN ACCESS EXCLUSIVE MODE; -- Access to administrators only IF NOT tt_user_access(actor, 'a') THEN SELECT -1 into errcode; -- Return E_NO_ACCESS RAISE EXCEPTION 'User % does not have access.', actor; END IF; -- Check if there are any active course areas with -- the given name. We do not allow duplicate names.. -- confusion may abound. IF tt_coursearea_name_active(area) THEN SELECT -2001 INTO errcode; -- E_DUP_COURSEAREA RAISE EXCEPTION 'Course area "%" already exists.', area; END IF; -- Grab a transaction ID SELECT tt_acquire_transaction(actor, 'tr_addcourse') INTO transid; IF transid < 0 THEN SELECT transid into errcode; -- Return the error code. RAISE EXCEPTION 'Could not acquire transaction.'; END IF; -- Insert the row INSERT INTO backend.courseareas (transactionid, active, caname) VALUES (transid, TRUE, area); RETURN 0; -- SUCCESS EXCEPTION WHEN RAISE_EXCEPTION THEN RETURN errcode; WHEN OTHERS THEN RETURN -32767; -- E_UNKNOWN END;$BODY$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER; -- Best regards, Ben Stewart -- Robert Bosch (Australia) Pty. Ltd. Engineering Quality Services, Student Software Engineer (RBAU/EQS4) Locked Bag 66 - Clayton South, VIC 3169 - AUSTRALIA mailto:[EMAIL PROTECTED] http://www.bosch.com.au/ ---(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
