[SQL] Update query by joining multiple tables.
Guys, on page this http://www.postgresql.org/docs/8.1/interactive/sql-update.html, it is not specified if a join can be done between multiple tables to update a table, i tried and it worked just fine for me. Something like this : Infact update can work between multiple tables to... do something like : UPDATE employees SET sales_count = a.sales_count FROM accounts as a WHERE a.name = 'Acme Corporation' AND employees.id = a.sales_person; considering sales_count as a column in accounts table. Regards, Moiz
[SQL] Postgresql & Oracle Heteregenous services - strange behaviour
I'm not sure if it's proper group, if not then please direct me to more
appropriate one (unixodbc?)
I've set up connection from oracle to postgresql via unixodbc and oracle's
heterogeneus services. The connection works, but when I issue the
following:
select "p_nr_pesel" from "zew_patients"@my_postgresql where
"p_patient_id"=19300;
I see this in postgresql logs:
[6210] DEBUG: query: select * from "zew_patients"
[6210] DEBUG: query: SELECT "A1"."p_nr_pesel" FROM "zew_patients"
"A1" WHERE ("A1"."p_patient_id" = 19300)
The point is, I don't why oh why the first select is issued (some cache?),
but it slows the query a lot for obvious reasons. When I issue it for the
second time in the same session, I see only the second select (that's how
it should be from my point of view). After reconnecting I see both selects
again :(.
If it's known issue, I'd be grateful even for simple RTFM and a link :).
Thank you for your time,
Marcin
--
| 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 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Update query by joining multiple tables.
On Wed, Jan 17, 2007 at 04:50:18PM +0530, Moiz Kothari wrote: > http://www.postgresql.org/docs/8.1/interactive/sql-update.html, it is not > specified if a join can be done between multiple tables to update a table, i Sure it is: A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. a -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(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] Postgresql & Oracle Heteregenous services - strange behaviour
On Wed, Jan 17, 2007 at 01:52:25PM +0100, Marcin St?pnicki wrote:
> I see this in postgresql logs:
>
> [6210] DEBUG: query: select * from "zew_patients"
> [6210] DEBUG: query: SELECT "A1"."p_nr_pesel" FROM "zew_patients"
> "A1" WHERE ("A1"."p_patient_id" = 19300)
>
> The point is, I don't why oh why the first select is issued (some cache?),
I bet it's getting the column list from the table or some such thing.
This is a lousy way to do it (the information_schema would be more
correct, although maybe no faster).
The reason it isn't repeated, I bet, is that your connection is
persistent, so the information gets cached.
A
--
Andrew Sullivan | [EMAIL PROTECTED]
Everything that happens in the world happens at some place.
--Jane Jacobs
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Postgresql & Oracle Heteregenous services - strange behaviour
Andrew Sullivan wrote:
On Wed, Jan 17, 2007 at 01:52:25PM +0100, Marcin St?pnicki wrote:
I see this in postgresql logs:
[6210] DEBUG: query: select * from "zew_patients"
[6210] DEBUG: query: SELECT "A1"."p_nr_pesel" FROM "zew_patients"
"A1" WHERE ("A1"."p_patient_id" = 19300)
The point is, I don't why oh why the first select is issued (some cache?),
I bet it's getting the column list from the table or some such thing.
This is a lousy way to do it (the information_schema would be more
correct, although maybe no faster).
That'd be my guess. And then it's not fetching any rows, expecting
cursor-like behaviour. Of course we fetch all the rows before returning
any results.
The real solution would be to add "LIMIT 0" or "LIMIT 1" to the
column-finding query, but I doubt that's possible with the Oracle plugin.
Perhaps check if there's a "fetch N rows at a time" option for the ODBC
setup that might help you.
--
Richard Huxton
Archonet Ltd
---(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] Postgresql & Oracle Heteregenous services - strange behaviour
Dnia Wed, 17 Jan 2007 13:04:28 +, Richard Huxton napisał(a): > Andrew Sullivan wrote: >> I bet it's getting the column list from the table or some such thing. >> This is a lousy way to do it (the information_schema would be more >> correct, although maybe no faster). > > That'd be my guess. And then it's not fetching any rows, expecting > cursor-like behaviour. Of course we fetch all the rows before returning > any results. Bingo! select * from from "zew_patients"@my_postgresql results in select a1.col1, a1.col2, a1.col3 from zew_patients in postgresql logs. > The real solution would be to add "LIMIT 0" or "LIMIT 1" to the > column-finding query, but I doubt that's possible with the Oracle plugin. > Perhaps check if there's a "fetch N rows at a time" option for the ODBC > setup that might help you. Thank you both, I'll poke around and drop a note when I find something :). -- | 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 2: Don't 'kill -9' the postmaster
Re: [SQL] Update query by joining multiple tables.
Might be a view and then a rule attached with that can help you out with doing updates using joins -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 1/17/07, Andrew Sullivan <[EMAIL PROTECTED]> wrote: On Wed, Jan 17, 2007 at 04:50:18PM +0530, Moiz Kothari wrote: > http://www.postgresql.org/docs/8.1/interactive/sql-update.html, it is not > specified if a join can be done between multiple tables to update a table, i Sure it is: A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. a -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(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] Some help with functions-syntax
I have some problem with writing a function. I have made this function which I can't get it to work. This is probaly a triviel question but i'm new to plsql, so this is a showstopper for me. DECLARE _relkind char; _RES "EMS"."KeySet"; _WHERECLAUSE text; BEGIN SELECT pgc.relkind INTO _relkind FROM pg_class pgc JOIN pg_namespace pgn ON pgc.relnamespace=pgn.oid WHERE pgn.nspname=$1 AND pgc.relname=$2; IF _relkind = 'r' THEN _WHERECLAUSE := '(fknam.nspname = ''$1'' AND fkc.relname = ''$2'')'; END IF; IF _relkind = 'v' THEN _WHERECLAUSE := ''; END IF; FOR _RES IN SELECT fknam.nspname AS "FKTABLE_SCHEM", fkc.relname AS "FKTABLE_NAME", fka.attname AS "FKCOLUMN_NAME", pknam.nspname AS "PKTABLE_SCHEM", pkc.relname AS "PKTABLE_NAME", pka.attname AS "PKCOLUMN_NAME", fkcon.conname AS "FK_NAME", pkcon.conname AS "PK_NAME", CASE WHEN pkcon.contype = 'p' THEN 'PRIMARY' WHEN pkcon.contype = 'u' THEN 'UNIQUE' END AS "UNIQUE_OR_PRIMARY" FROMpg_constraint AS fkcon JOIN pg_namespace AS fknam ON fkcon.connamespace=fknam.oid JOIN pg_class AS fkc ON fkc.oid=fkcon.conrelid JOIN pg_attribute fka ON fka.attrelid=fkc.oid AND fka.attnum = ANY(fkcon.conkey) JOIN pg_constraint AS pkcon ON fkcon.confrelid=pkcon.conrelid AND fkcon.confkey=pkcon.conkey JOIN pg_namespace pknam ON pkcon.connamespace=pknam.oid JOIN pg_class pkc ON pkc.oid=fkcon.confrelid JOIN pg_attribute pka ON pka.attrelid=pkc.oid AND pka.attnum = some(pkcon.conkey) WHERE ( (pkcon.conkey[1] = pka.attnum AND fkcon.conkey[1] = fka.attnum) OR (pkcon.conkey[2] = pka.attnum AND fkcon.conkey[2] = fka.attnum) OR (pkcon.conkey[3] = pka.attnum AND fkcon.conkey[3] = fka.attnum) OR (pkcon.conkey[4] = pka.attnum AND fkcon.conkey[4] = fka.attnum) OR (pkcon.conkey[5] = pka.attnum AND fkcon.conkey[5] = fka.attnum) ) AND || _WHERECLAUSE LOOP RETURN NEXT _RES; END LOOP; RETURN; END; I get the following error: ERROR: operator does not exist: || text SQL state: 42883 Hint: No operator matches the given name and argument type(s). You may need to add explicit type casts. How do I solve this? I also have a question about how to write CASE-statement? I am only able to use if. Regards Jan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Some help with functions-syntax
Take a look at the documentation and examples again: http://www.postgresql.org/docs/8.2/interactive/plpgsql-control- structures.html#PLPGSQL-RECORDS-ITERATING There are two forms to iterate over the query: 1. FOR target IN query LOOP 2. FOR target IN EXECUTE text_expression LOOP In your code you have mixed the two together. You appear to be trying to concatenate a string on to the end of a query expression. My suggestion is to eliminate the string you created (_WHERECLAUSE) and add the proper conditions to the query expression you already have. On Jan 17, 2007, at 5:33 PM, Jan Meyland Andersen wrote: I have some problem with writing a function. I have made this function which I can't get it to work. This is probaly a triviel question but i'm new to plsql, so this is a showstopper for me. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Some help with functions-syntax
> There are two forms to iterate over the query: > > 1. FOR target IN query LOOP > 2. FOR target IN EXECUTE text_expression LOOP Thanks for your answer. > My suggestion is to eliminate the string you created (_WHERECLAUSE) and > add the proper conditions to the query expression you already have. But the problem here is that the where-clause depends on the relkind. That is why I'm trying to solve the problem this way. How do I then write EXECUTE queries on multiple lines, if I go with this solution? Regards Jan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
