[SQL] sub query
Hi I have this problem, when I try to run this query: SELECT MAX(d), host_position FROM (SELECT host_position, COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e; am getting and ERROR: Attribute e.host_position must be GROUPed or used in an aggregate function. Please to advice what could be the problem and how can I rewrite it to work thanks in advance. Kind regards +-+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] ++ _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] sub query
Hi I have this problem, when I try to run this query: SELECT MAX(d), host_position FROM (SELECT host_position, COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e; am getting and ERROR: Attribute e.host_position must be GROUPed or used in an aggregate function. Please to advice what could be the problem and how can I rewrite it to work thanks in advance. Kind regards +-+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] ++ _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] sub query
> Hi I have this problem, when I try to run this query: > > SELECT MAX(d), host_position FROM (SELECT host_position, > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e; > > am getting and ERROR: Attribute e.host_position must be GROUPed or used in > an aggregate function. > > Please to advice what could be the problem and how can I rewrite it to work > thanks in advance. > As the error message says: e.host_position must be GROUPed so (supposing you want a one row result showing the maximum count) SELECT MAX(e.d), e.host_position FROM (SELECT host_position, COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e GROUP BY e.host_position ORDER BY 1 LIMIT 1; should match your intentions. Regards, Christoph ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] sub query
> > > Hi I have this problem, when I try to run this query: > > > > SELECT MAX(d), host_position FROM (SELECT host_position, > > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e; > > > > am getting and ERROR: Attribute e.host_position must be GROUPed or > used in > > an aggregate function. > > > > Please to advice what could be the problem and how can I rewrite it to > work > > thanks in advance. > > > As the error message says: e.host_position must be GROUPed > > so (supposing you want a one row result showing the maximum count) > > SELECT MAX(e.d), e.host_position FROM (SELECT host_position, > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e > GROUP BY e.host_position ORDER BY 1 LIMIT 1; > > should match your intentions. > Just thought about another (less complex) way: SELECT COUNT(host_position), host_position FROM sss_host GROUP BY host_position ORDER BY 1 DESC LIMIT 1; Regards, Christoph ---(end of broadcast)--- TIP 3: 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] Sort order with spaces?
Hello! I am having troubles with sort order in Postgres. It seems that space is not handled at all? For instance the following rows are sorted in MS SQL Server as: LUNDGREN M L R MACDOWELL MUSCLE But in Postgres I get this order: LUNDGREN MACDOWELL M L R MUSCLE I seems that space is no considered at all so "M L R" is interpreted as "MLR" i Postgres. This is a problem for me taht I need to resolve. How do I do that? Regards Kristian -- \\|// (@ @) +--ooO--(_)--Ooo--+--+ | Kristian Jörg | Phone: +46 54 153395 | | Devo IT AB | Fax: +46 54 153389 | | Box 533, SE-651 12 KARLSTAD | mailto:[EMAIL PROTECTED] | | Sweden | http://www.devo.se | +-+--+ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sub query
Thanks Haller, the second one worked thanks a million be blessed Regards +-+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] ++ From: Christoph Haller <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [SQL] sub query Date: Wed, 17 Sep 2003 10:54:49 +0200 > > > Hi I have this problem, when I try to run this query: > > > > SELECT MAX(d), host_position FROM (SELECT host_position, > > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e; > > > > am getting and ERROR: Attribute e.host_position must be GROUPed or > used in > > an aggregate function. > > > > Please to advice what could be the problem and how can I rewrite it to > work > > thanks in advance. > > > As the error message says: e.host_position must be GROUPed > > so (supposing you want a one row result showing the maximum count) > > SELECT MAX(e.d), e.host_position FROM (SELECT host_position, > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e > GROUP BY e.host_position ORDER BY 1 LIMIT 1; > > should match your intentions. > Just thought about another (less complex) way: SELECT COUNT(host_position), host_position FROM sss_host GROUP BY host_position ORDER BY 1 DESC LIMIT 1; Regards, Christoph ---(end of broadcast)--- TIP 3: 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 _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Sort order with spaces?
On Wednesday 17 September 2003 10:47, Kristian Jörg wrote: > Hello! > > I am having troubles with sort order in Postgres. It seems that space is > not handled at all? > For instance the following rows are sorted in MS SQL Server as: > > LUNDGREN > M L R > MACDOWELL > MUSCLE > > But in Postgres I get this order: > > LUNDGREN > MACDOWELL > M L R > MUSCLE Sort order depends upon your locale settings (specifically LC_COLLATE), which will have been set when you ran "initdb". Basically, sort orders for C , en_GB and fr will all be different. I'm guessing you expect "C" style sorting. Check the end of your postgresql.conf file to see what settings you currently have. See the manuals (Localization section) and list archives for plenty of details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Trigger order problems
TIA people Three related tables: trans_core - transaction details (trans_id, trans_owner, trans_date, trans_amount) trans_src - funding source (core_id, src_id, src_amount) src_summary - summary of funding-source totals (summary_date, summary_src, summary_amount) The total trans_amount must always equal the sum of the linked "src_amount"s and these funding-sources are totalled to the summary table. The problem: I want to update the summary table whenever trans_src is modified, but to do so I need to get information from trans_core (trans_owner, trans_date). Now that's not a problem with UPDATE or INSERT since the corresponding trans_core must exist. The problem is with DELETE. If I have the following sequence: 1. DELETE row from trans_core 2. Cascades to several DELETEs on trans_src 3. BEFORE DELETE trigger is called for each of these 4. Summary table is decremented using details from trans_src and trans_core Of course, the problem is that by step 4 there isn't a row in trans_core to refer to... Options I have considered: 1. Duplicate required fields from trans_core in trans_src (yuck - in the real tables there are several fields I'd need) 2. Wipe and recalculate all relevant summary info every time trans_core is modified and make sure that we touch trans_core every time trans_src gets updated. 3. Replace src_summary with a view. Can't do this, since some of the information is historical for which we don't have any transactions to back up the summary. I'm going with #2 at the moment, but it seems wasteful to recalculate more than I need to. Anyone got any smart ideas? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] sub query
The reason why the first one SELECT MAX(e.d), e.host_position FROM (SELECT host_position, COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e GROUP BY e.host_position ORDER BY 1 LIMIT 1; did not work is because the query needs a ORDER BY 1 DESC LIMIT 1 like the second one. Mind the DESCending order. Regards, Christoph > > Thanks Haller, the second one worked thanks a million be blessed > > > > > > > > Hi I have this problem, when I try to run this query: > > > > > > > > SELECT MAX(d), host_position FROM (SELECT host_position, > > > > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as > >e; > > > > > > > > am getting and ERROR: Attribute e.host_position must be GROUPed or > > > used in > > > > an aggregate function. > > > > > > > > Please to advice what could be the problem and how can I rewrite it > >to > > > work > > > > thanks in advance. > > > > > > > As the error message says: e.host_position must be GROUPed > > > > > > so (supposing you want a one row result showing the maximum count) > > > > > > SELECT MAX(e.d), e.host_position FROM (SELECT host_position, > > > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e > > > GROUP BY e.host_position ORDER BY 1 LIMIT 1; > > > > > > should match your intentions. > > > > >Just thought about another (less complex) way: > > > >SELECT COUNT(host_position), host_position FROM > >sss_host GROUP BY host_position ORDER BY 1 DESC LIMIT 1; > > ---(end of broadcast)--- TIP 3: 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] Sort order with spaces?
Richard Huxton wrote: On Wednesday 17 September 2003 10:47, Kristian Jörg wrote: Hello! I am having troubles with sort order in Postgres. It seems that space is not handled at all? For instance the following rows are sorted in MS SQL Server as: LUNDGREN M L R MACDOWELL MUSCLE But in Postgres I get this order: LUNDGREN MACDOWELL M L R MUSCLE Sort order depends upon your locale settings (specifically LC_COLLATE), which will have been set when you ran "initdb". Basically, sort orders for C , en_GB and fr will all be different. I'm guessing you expect "C" style sorting. Check the end of your postgresql.conf file to see what settings you currently have. See the manuals (Localization section) and list archives for plenty of details. Hi Richard! Unfortunately this does not seem to help! I dumped the database, recreated the cluster with initdb with LC_COLLATE = C (and even tried setting LC_CTYPE to C also), and restored the database. The same ordering appears... I did look through all the manuals and I noted that the method for setting locale is different for 7.2 and 7.3, so I followed the 7.2 docs. My postgres version is 7.2. The table keeps the data above in a column named "NORMTEXT" and my SQL statement for the result above is: select * from mytable where NORMTEXT >= 'LU' Nothing out of the ordinary there.. Any help on this matter is highly appreciated! Regards Kristian -- \\|// (@ @) +--ooO--(_)--Ooo--+--+ | Kristian Jörg | Phone: +46 54 153395 | | Devo IT AB | Fax: +46 54 153389 | | Box 533, SE-651 12 KARLSTAD | mailto:[EMAIL PROTECTED] | | Sweden | http://www.devo.se | +-+--+
Re: [SQL] Sort order with spaces?
Kristian Jörg wrote: Richard Huxton wrote: On Wednesday 17 September 2003 10:47, Kristian Jörg wrote: Hello! I am having troubles with sort order in Postgres. It seems that space is not handled at all? For instance the following rows are sorted in MS SQL Server as: LUNDGREN M L R MACDOWELL MUSCLE But in Postgres I get this order: LUNDGREN MACDOWELL M L R MUSCLE Sort order depends upon your locale settings (specifically LC_COLLATE), which will have been set when you ran "initdb". Basically, sort orders for C , en_GB and fr will all be different. I'm guessing you expect "C" style sorting. Check the end of your postgresql.conf file to see what settings you currently have. See the manuals (Localization section) and list archives for plenty of details. Hi Richard! Unfortunately this does not seem to help! I dumped the database, recreated the cluster with initdb with LC_COLLATE = C (and even tried setting LC_CTYPE to C also), and restored the database. The same ordering appears... I did look through all the manuals and I noted that the method for setting locale is different for 7.2 and 7.3, so I followed the 7.2 docs. My postgres version is 7.2. The table keeps the data above in a column named "NORMTEXT" and my SQL statement for the result above is: select * from mytable where NORMTEXT >= 'LU' Nothing out of the ordinary there.. Any help on this matter is highly appreciated! Woops, a type above. My sql is of course: select * from mytable where normtext>= 'LU' order by normtext; Regards Kristian -- \\|// (@ @) +--ooO--(_)--Ooo--+--+ | Kristian Jörg | Phone: +46 54 153395 | | Devo IT AB | Fax: +46 54 153389 | | Box 533, SE-651 12 KARLSTAD | mailto:[EMAIL PROTECTED] | | Sweden | http://www.devo.se | +-+--+ -- \\|// (@ @) +--ooO--(_)--Ooo--+--+ | Kristian Jörg | Phone: +46 54 153395 | | Devo IT AB | Fax: +46 54 153389 | | Box 533, SE-651 12 KARLSTAD | mailto:[EMAIL PROTECTED] | | Sweden | http://www.devo.se | +-+--+
Re: [SQL] Sort order with spaces?
On Wednesday 17 September 2003 13:39, Kristian Jörg wrote: > Kristian Jörg wrote: > > Richard Huxton wrote: > >>> > >>>LUNDGREN > >>>M L R > >>>MACDOWELL > >>>MUSCLE > >>> > >>>But in Postgres I get this order: > >>> > >>>LUNDGREN > >>>MACDOWELL > >>>M L R > >>>MUSCLE > >> > Woops, a type above. My sql is of course: > select * from mytable where normtext>= 'LU' order by normtext; richtest=# select * from foo order by b; a | b ---+--- 1 | LUNDGREN 2 | M L R 3 | MACDOWELL 4 | MUSCLE (4 rows) LC_MESSAGES = 'C' LC_MONETARY = 'C' LC_NUMERIC = 'C' LC_TIME = 'C' Might be worth checking the release notes on the website to see if there's anything mentioned. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sort order with spaces?
=?ISO-8859-1?Q?Kristian_J=F6rg?= <[EMAIL PROTECTED]> writes: > Unfortunately this does not seem to help! I dumped the database, > recreated the cluster with initdb with LC_COLLATE = C (and even tried > setting LC_CTYPE to C also), and restored the database. The same > ordering appears... You didn't do it right then ... 7.2's initdb is not helpful about telling you exactly what locale settings it's using, but you could use the contrib/pg_controldata utility to check what LC_COLLATE and LC_CTYPE settings got used. (If you don't want to build pg_controldata, "strings $PGDATA/global/pg_control" will do as a rough-and-ready substitute.) Theoretically it should work to do export LC_COLLATE=C export LC_CTYPE=C initdb but if you have LANG or other LC_xxx values in your environment, it's possible that there is some conflict. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Sort order with spaces?
Tom Lane wrote: =?ISO-8859-1?Q?Kristian_J=F6rg?= <[EMAIL PROTECTED]> writes: Unfortunately this does not seem to help! I dumped the database, recreated the cluster with initdb with LC_COLLATE = C (and even tried setting LC_CTYPE to C also), and restored the database. The same ordering appears... You didn't do it right then ... 7.2's initdb is not helpful about telling you exactly what locale settings it's using, but you could use the contrib/pg_controldata utility to check what LC_COLLATE and LC_CTYPE settings got used. (If you don't want to build pg_controldata, "strings $PGDATA/global/pg_control" will do as a rough-and-ready substitute.) Unfortunately I am using a binary distribution from a RPM package, so I don't have the source. Tried the strings stuff though and that worked. I have sv_SE as locale for sure. So you are corect. I did not do initdb correctly. Shame on me... :-) Theoretically it should work to do export LC_COLLATE=C export LC_CTYPE=C initdb but if you have LANG or other LC_xxx values in your environment, it's possible that there is some conflict. regards, tom lane It is possible my locale settings did not get through to initdb since I tried using the startup postgres command in /etc/init.d (with som modifications of course). I will try again manually this time! Regards Kristian . -- \\|// (@ @) +--ooO--(_)--Ooo--+--+ | Kristian Jörg | Phone: +46 54 153395 | | Devo IT AB | Fax: +46 54 153389 | | Box 533, SE-651 12 KARLSTAD | mailto:[EMAIL PROTECTED] | | Sweden | http://www.devo.se | +-+--+
Re: [SQL] Sort order with spaces?
Tom and Richard, I have successfully fixed this problem now thanks to your support! The setting of LC_COLLATE to C prior to running initdb fixed all problems finally once I had sorted out my error in passing the environment variables correctly. Thanx! /Kristian Kristian Jörg wrote: Tom Lane wrote: =?ISO-8859-1?Q?Kristian_J=F6rg?= <[EMAIL PROTECTED]> writes: Unfortunately this does not seem to help! I dumped the database, recreated the cluster with initdb with LC_COLLATE = C (and even tried setting LC_CTYPE to C also), and restored the database. The same ordering appears... You didn't do it right then ... 7.2's initdb is not helpful about telling you exactly what locale settings it's using, but you could use the contrib/pg_controldata utility to check what LC_COLLATE and LC_CTYPE settings got used. (If you don't want to build pg_controldata, "strings $PGDATA/global/pg_control" will do as a rough-and-ready substitute.) Unfortunately I am using a binary distribution from a RPM package, so I don't have the source. Tried the strings stuff though and that worked. I have sv_SE as locale for sure. So you are corect. I did not do initdb correctly. Shame on me... :-) Theoretically it should work to do export LC_COLLATE=C export LC_CTYPE=C initdb but if you have LANG or other LC_xxx values in your environment, it's possible that there is some conflict. regards, tom lane It is possible my locale settings did not get through to initdb since I tried using the startup postgres command in /etc/init.d (with som modifications of course). I will try again manually this time! Regards Kristian . -- \\|// (@ @) +--ooO--(_)--Ooo--+--+ | Kristian Jörg | Phone: +46 54 153395 | | Devo IT AB | Fax: +46 54 153389 | | Box 533, SE-651 12 KARLSTAD | mailto:[EMAIL PROTECTED] | | Sweden | http://www.devo.se | +-+--+ -- \\|// (@ @) +--ooO--(_)--Ooo--+--+ | Kristian Jörg | Phone: +46 54 153395 | | Devo IT AB | Fax: +46 54 153389 | | Box 533, SE-651 12 KARLSTAD | mailto:[EMAIL PROTECTED] | | Sweden | http://www.devo.se | +-+--+
[SQL] Triggers Help...
Hi, I have two databases with the same tables, one is my Real Database and the other is my Log Database. My Log database does NOT have any kind of keys (PRIMARY or FOREIGN). I need to insert from my Real database to my Log database every row that is Updated or Deleted in any Table of the Real database. I need to create triggers (BEFORE DELETE or BEFORE UPDATE) to each of my Real database tables, these triggers will INSERT into my Log database table the row that is being deleted or/and updated in the Real Database tables. I dont know how to do this? Anyone can help me? Thanks, Tiago Alves ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how to get decimal to date form
On Wednesday 17 Sep 2003 13:50 in <[EMAIL PROTECTED]>, wireless ([EMAIL PROTECTED]) wrote: > For example where the rec_num is 30608124143.47069519725 the above > functions return 30608. Unless you are logging dates around 3 A.D., I suggest you add 2000 to the number before you apply the TO_CHAR() function. I strongly suspect your app is not Y2K-compliant. [I haven't written that for years!] -- Regards, Dave [RLU#314465] == [EMAIL PROTECTED] (David W Noon) Remove spam trap to reply via e-mail. == ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] [BUGS] session variable
[EMAIL PROTECTED] (Bruce Momjian) wrote in message news:<[EMAIL PROTECTED]>... > Yes, see the FAQ. You have to use EXECUTE for temp table access in > functions. Unfortunately in the 7.2.x version I have available, EXECUTE does not allow "select into". The docs suggests a technique for using a FOR loop to extract information from an EXECUTE, but the technique only works for finite set of expected variables, not for arbitrary values. Any suggestions on how to kludge this issue? It would be of great value to me if there were a way to set and retrieve just one global variable that persists throughout a session, and which can be retrieved from a function. ---(end of broadcast)--- TIP 3: 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] Trigger on view
I'm trying to set up a trigger that restricts when and how updates are done to the records in a relation. However, when the administrator accesses the relation, I want the restriction to be relaxed. My approach was to have a view that is accessed by the normal users and have the admin access the table directly. However, it doesn't appear that I can actually apply a trigger to a view. Some of this I can do with rules on the view, but certain restrictions are applicable only on a row-by-row basis, so I think I need to do it in a BEFORE trigger. Is there a way I can either: 1. Apply a trigger to a view. The trigger would actually get registered with the underlying table, but would only be invoked if the user had entered via the view. 2. Detect inside the trigger function whether the user had accessed the table directly or through the view so I could invoke conditional code accordingly. I want to avoid hardcoding user ID's in the trigger, so I'm trying to take this approach so it can all be handled simply by granting privileges on different views to enable/disable the varying levels of restriction. Any ideas? Kyle Bateman ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] how to get decimal to date form
In our postgre database is a decimal field with format YYMMDDhhmmss.99 where the 9s are random digits. I'm trying to strip off just the YYMMDD and put it in date form. So far I came up with: SUBSTR(TO_CHAR(rec_num,999),1,6) AS Date which returns YMMDD. For example where the rec_num is 30608124143.47069519725 the above functions return 30608. I tried wrapping another TO_CHAR around it to try to format it to a date but this seems like it's a bit much for this purpose. Any suggestions would be appreciated. -David ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Array fields in Postgresql...
Hi all, Can any one give me a link/tutorial for using arrays in databases..I need Queries to access the arrays as well... Thanx in advance. regards, Jagdeesh. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] how to get decimal to date form
In our postgre database is a decimal field with format YYMMDDhhmmss.99
where the 9s are random digits. I'm trying to strip off just the
YYMMDD and put it in date form.
So far I came up with:
SUBSTR(TO_CHAR(rec_num,999),1,6) AS Date which returns YMMDD.
For example where the rec_num is 30608124143.47069519725 the above
functions return 30608.
I tried wrapping another TO_CHAR around it to try to format it to a
date but this seems like it's a bit much for this purpose.
Any suggestions would be appreciated.
-David
1. replace 0 with 9 to get leading zeroes - 030608 instead of 30608
2. to_date('030608','YYMMDD');
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] cursors in plpgsql
Hi I wanted to use some select result several times in pl/pgsql function. DECLARE test refcursor; x record; BEGIN open test for select... fetch test into x; while found loop ...work... fetch test into x; end loop; ...rewind cursor using move... fetch test... Neither move backward 1 in test; nor execute ''move backward 1 in test''; doesn't work. In first case I get: ERROR: parser: parse error at or near "$1"... In secod: ERROR: unexpected error -5 in EXECUTE of query... I tried also with "perform" - I got no error, but also no valid result. What should I do with it? Or maybe is it possible somehow to use cursor in FOR..IN loop? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] cursors in plpgsql
Tomasz Myrta <[EMAIL PROTECTED]> writes: > I wanted to use some select result several times in pl/pgsql function. > Neither move backward 1 in test; > nor > execute ''move backward 1 in test''; > doesn't work. Releases before 7.4 are spotty about supporting backwards scan of complex queries --- if you have a join or aggregate in the query, it likely won't work, yielding either strange errors or wrong answers. It will work if the top plan node in the query is a SORT, though, so a possible workaround is to add an explicit ORDER BY to the cursor's query. (You will need to do some investigation with EXPLAIN to make sure you are getting a suitable plan for the cursor.) Or try 7.4 beta ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] [BUGS] session variable
[EMAIL PROTECTED] (Miko O'Sullivan) writes: > Unfortunately in the 7.2.x version I have available, EXECUTE does not > allow "select into". The docs suggests a technique for using a FOR > loop to extract information from an EXECUTE, but the technique only > works for finite set of expected variables, not for arbitrary values. How do you figure that? AFAICS "FOR" with a record variable as target will cover any case that SELECT INTO could handle. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Triggers Help...
[EMAIL PROTECTED] wrote: Hi, I have two databases with the same tables, one is my Real Database and the other is my Log Database. My Log database does NOT have any kind of keys (PRIMARY or FOREIGN). I need to insert from my Real database to my Log database every row that is Updated or Deleted in any Table of the Real database. I need to create triggers (BEFORE DELETE or BEFORE UPDATE) to each of my Real database tables, these triggers will INSERT into my Log database table the row that is being deleted or/and updated in the Real Database tables. I dont know how to do this? Anyone can help me? What you are looking for is a replication tools, look here: http://www.postgresql.org/news/147.html Regards Gaetano Mendola ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] transaction locking
hello all
i am (probably) shooting myself in the foot, but here goes the question.
inside of a begin transaction/commit block, i am attempting to insert a record
into a parts table, and then insert a record that references the part into an
application table. and the transaction fails with this error message:
ERROR: app_part_ins_trig referential integrity violation - key referenced from
application not found in parts
i understand that since the record was inserted into the parts table *after*
the BEGIN TRANSACTION statement, the insert into the application table cannot
see that a record exists until a commit.
any suggestions are greatly appreciated.
the tables are as shown:
CREATE TABLE parts (
make character varying(16) DEFAULT 'AMC' NOT NULL,
amc_part_no character varying(8) NOT NULL,
group_no character varying(2) NOT NULL,
subgroup_no character varying(8),
part_name character varying(32) DEFAULT '' NOT NULL,
description character varying(255),
prim_grp character(2),
prim_sbg character(8),
no_req integer,
weight numeric(6,2),
count integer DEFAULT 0,
ordered integer DEFAULT 0,
cost numeric(6,2),
price numeric(6,2),
graph character varying(128),
Constraint parts_pkey Primary Key (make, amc_part_no)
);
CREATE TABLE application (
make character varying(16) DEFAULT 'AMC' NOT NULL,
amc_part_no character varying(8) NOT NULL,
year integer NOT NULL,
model character varying(2) NOT NULL,
Constraint application_pkey Primary Key (make, amc_part_no, year, model)
);
and the constraint that is causing problems is:
CREATE CONSTRAINT TRIGGER app_part_ins_trig AFTER INSERT OR UPDATE ON
application FROM parts NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins" ('app_part_ins_trig', 'application',
'parts', 'UNSPECIFIED', 'make', 'make', 'amc_part_no', 'amc_part_no');
inside the program in question, i do a
$res=pg_query( "BEGIN TRANSACTION" ) ;
if (strlen ($r=pg_last_error( $db ) ) )
{
$replaces.= $r."\n" ;
pg_connection_reset( $db ) ;
$failed = -1 ;
}
$sql = "LOCK TABLE parts IN SHARE ROW EXCLUSIVE MODE" ;
$res = pg_query( $sql ) ;
if (strlen ($r=pg_last_error( $db ) ) )
{
$replaces.="SQL: $sql\n" ;
$replaces.= $r."\n" ;
pg_connection_reset( $db ) ;
$failed = -1 ;
}
$sql = "SET CONSTRAINTS ALL DEFERRED" ;
$res = pg_query( $sql ) ;
if (strlen ($r=pg_last_error( $db ) ) )
{
$replaces.="SQL: $sql\n" ;
$replaces.= $r."\n" ;
pg_connection_reset( $db ) ;
$failed = -1 ;
}
if ( $failed == 0 )
{
...
--
tia,
tom baker
former ingres programmer...
I'm using my X-RAY VISION to obtain a rare glimpse of the INNER
WORKINGS of this POTATO!!
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] transaction locking
On Wed, 17 Sep 2003, tom baker wrote:
> i am (probably) shooting myself in the foot, but here goes the question.
>
> inside of a begin transaction/commit block, i am attempting to insert a record
> into a parts table, and then insert a record that references the part into an
> application table. and the transaction fails with this error message:
>
> ERROR: app_part_ins_trig referential integrity violation - key referenced from
> application not found in parts
>
> i understand that since the record was inserted into the parts table *after*
> the BEGIN TRANSACTION statement, the insert into the application table cannot
> see that a record exists until a commit.
Assuming that they are both in the same transaction, the second insert
should be able to see the results of the first insert, can you send an
example sequence of inserts as well? (Also see the note below)
> and the constraint that is causing problems is:
> CREATE CONSTRAINT TRIGGER app_part_ins_trig AFTER INSERT OR UPDATE ON
> application FROM parts NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW
> EXECUTE PROCEDURE "RI_FKey_check_ins" ('app_part_ins_trig', 'application',
> 'parts', 'UNSPECIFIED', 'make', 'make', 'amc_part_no', 'amc_part_no');
>
> $sql = "SET CONSTRAINTS ALL DEFERRED" ;
I'm not sure if you know, but this is not going to deferr the constraint
above because it was created with NOT DEFERRABLE.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] cursors in plpgsql
Releases before 7.4 are spotty about supporting backwards scan of complex queries --- if you have a join or aggregate in the query, it likely won't work, yielding either strange errors or wrong answers. It will work if the top plan node in the query is a SORT, though, so a possible workaround is to add an explicit ORDER BY to the cursor's query. (You will need to do some investigation with EXPLAIN to make sure you are getting a suitable plan for the cursor.) I rewrote my query to have sort in top of plan: Sort (cost=151.24..151.25 rows=1 width=36) Sort Key: czas -> Aggregate (cost=151.22..151.23 rows=1 width=36) -> Group (cost=151.22..151.23 rows=1 width=36) -> Sort (cost=151.22..151.22 rows=1 width=36) I'm not sure if it is what you were talking about, but it didn't help. Anyway the best choice for this function would be a C function, but SPI scares me... And one more question - which syntax is valid? move backward.. or execute ''move backward... Or try 7.4 beta ... Currently stable branches are better for me... regards, tom lane Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
