[SQL] left join in cursor
Hi.
Exists too tables (PostgreSQL 7.2.3):
a ("cl" integer primary key, a1, a2, ...) - 10 records;
b ("cl" integer primary key, b1, b2, ...) - 800 records.
BEGIN;
DECLARE "c" SCROLL CURSOR FOR select a.*,r.b1 as rb1 from a left join b using
(cl) order by a.cl;
FETCH FORWARD 100 in "c";
FETCH FORWARD 100 in "c";
MOVE -200 in "c";
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
Without left join - all rights.
Why?
Thanks in advance.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Retype
On Monday 09 Jun 2003 10:24 am, Rado Petrik wrote: > I have query " SELECT id_user FROM user WHERE (freg_u & 2 ) > 0 ". > > freg_u is type smallint. > How I retype freg_u to interger ? ... WHERE (freg_u::integer & 2) ... Or you could use the more standard but long-winded CAST function -- Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] how to determine array size
On Mon, 9 Jun 2003, Forest Wilkinson wrote:
> I need to enumerate the constraints on any given column in a table, so
> I'm examining pg_constraint to get the relevant information. The
> conkey array contains a list of constrained columns, and although I am
> able to check conkey[1] for constraints on a single column, I would
> like to properly handle multi-column constraints.
>
> How do I determine the size of the conkey array? I haven't found any
> field that looks like it contains the number of values in conkey. Do
> I have to check each element of the array sequentially, until I get a
> NULL value from one of them? (Section 5.12 of the User's Guide seems
> to forbid this: "A limitation of the present array implementation is
> that individual elements of an array cannot be SQL null values.")
> Moreover, that method doesn't give me a nice way of selecting all
> constraints on a specific column, as I would have to write clauses
> like this:
>
> ... WHERE conkey[1] = blah OR conkey[2] = blah OR conkey[3] = blah OR
> conkey[4] = blah ...
>
> Can somone offer a better way?
Well if you are willing to extend contrib package intarray
to something like smallintarray
you could simply do
SELECT conname from pg_constraint where conrelid= and
'{blah}' ~ conkey;
Or as a quick solution create your own function
boolean isinarr(smallint,smallint[])
that performs this task, and do
SELECT conname from pg_constraint where conrelid= and
isinarr(blah,conkey);
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-210-8981112
fax:+30-210-8981877
email: achill at matrix dot gatewaynet dot com
mantzios at softlab dot ece dot ntua dot gr
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Coalesce/Join/Entries may not exist.
On Mon, 2003-06-09 at 08:00, James Taylor wrote: > I've got three tables, I'll shorten the columns down just so you get the > idea: > > lists > --- > id|order_id > > list_results > > id|lid|total > > orders > > id|max > > All of the columns are int's. What I'm trying to do is something like: > > select (o.max-coalesce(sum(lr.total),0)) from orders o,list_results lr > where lr.l_id in (select >l.id from lists l, orders o where l.order_id=X and o.id=l.order_id) > group by o.max > > This would, in theory, return a number which should be > o.total-sum(lr.total) > > The problem is, there may not be any data in list_results OR lists > regarding the order ID. If data from list_results.total exists, and is > referencing lists.id, which in turn is referencing orders.id through > lists.order_id, return o.max-lr.total. If data from list_results or > lists DOESN'T exist, I would just want to go ahead and return > orders.max. I was hoping the coalesce would be able to do this, but it > doesn't. The subquery is in there because frankly I'm not sure how to > do multiple left joins, which I think would have to exist. The easy way > out for me here I think would be to make list_results.order_id and leave > lists out of it, but then I'd have redundant data in two tables. Any > suggestions on this one? Multiple left outer joins? FROM LEFT OUTER JOIN USING () LEFT OUTER JOIN USING () WHERE ... In your case, SELECT (o.max - coalesce(sum(lr.total), 0)) FROM orders LEFT OUTER JOIN lists ON (orders.id = lists.order_id) LEFT OUTER JOIN list_results ON (lists.id = list_results.l_id) GROUP BY o.max But I'm not sure if that accomplishes what you're looking for or not. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] how to determine array size
On Mon, 2003-06-09 at 16:23, Forest Wilkinson wrote: > I need to enumerate the constraints on any given column in a table, so > I'm examining pg_constraint to get the relevant information. The > conkey array contains a list of constrained columns, and although I am > able to check conkey[1] for constraints on a single column, I would > like to properly handle multi-column constraints. You may have an easier time dealing with pg_get_constraintdef() than trying to get the info from the source. The above function outputs the necessary SQL to rebuild the constraint, and is used by pg_dump. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] left join in cursor
Alexey Dashevsky <[EMAIL PROTECTED]> writes: > DECLARE "c" SCROLL CURSOR FOR select a.*,r.b1 as rb1 from a left join b using > (cl) order by a.cl; > FETCH FORWARD 100 in "c"; > FETCH FORWARD 100 in "c"; > MOVE -200 in "c"; > [ core dump ] Some plan node types don't cope very well with being run backwards. There is a workaround for this in CVS tip, but not in 7.2.* nor (IIRC) in 7.3.*. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] "Join" on delimeter aggregate query
On Mon, Jun 09, 2003 at 10:35:10 +0200, Eivind Kvedalen <[EMAIL PROTECTED]> wrote: > > Ok. What I actually had in mind was whether the optimizer would remove the > ORDER BY clause completely or not, as it isn't used in the top-level > SELECT query, and SQL doesn't in general guarantee ordered rows back > unless there's an ORDER BY in the top-level SELECT (I haven't read the SQL > standard, so I might very well be wrong here). Allowing ordering in subselects is a feature (mostly for use with LIMIT) and won't be removed because it isn't a top level select. ---(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 error
Hi, I',m trying to create trigger with plpgsql trigger function then I got this error message when trigger executed: Number: -2147467259 Error while executing th query; ERROR: fmgr_info: function 1546856080: cache lookup failed Here is the function code: CREATE FUNCTION TRIGGER_UPDATE_AXPRDT_STATUS()RETURNS OPAQUEAS 'BEGIN IF NEW. THEN NEW.STATUS = ''D''; END IF; RETURN NEW;END;'LANGUAGE 'plpgsql'; CREATE TRIGGER TRIGGER_AXPRDT_FUTURESTATUSBEFORE UPDATE ON AXPRDTFOR EACH ROWEXECUTE PROCEDURE TRIGGER_UPDATE_AXPRDT_STATUS(); Please Help.. Yudie
Re: [SQL] trigger error
You must have dropped and recreated the function after your trigger was created... You need to recreate the trigger now, so that it picks up the new function id. In the future use 'CREATE OR REPLACE' to modify a function instead of DROP and CREATE - this will make sure the modified function keeps its id. I hope, it helps... Dima Yudie wrote: Hi, I',m trying to create trigger with plpgsql trigger function then I got this error message when trigger executed: Number: -2147467259 Error while executing th query; ERROR: fmgr_info: function 1546856080: cache lookup failed Here is the function code: CREATE FUNCTION TRIGGER_UPDATE_AXPRDT_STATUS() RETURNS OPAQUE AS 'BEGIN IF NEW.ONHAND = 0 THEN NEW.STATUS = ''D''; END IF; RETURN NEW; END;' LANGUAGE 'plpgsql'; CREATE TRIGGER TRIGGER_AXPRDT_FUTURESTATUS BEFORE UPDATE ON AXPRDT FOR EACH ROW EXECUTE PROCEDURE TRIGGER_UPDATE_AXPRDT_STATUS(); Please Help.. Yudie ---(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] trigger error
"Yudie" <[EMAIL PROTECTED]> wrote: > Hi, > I',m trying to create trigger with plpgsql trigger function > then I got this error message when trigger executed: > Number: -2147467259 > Error while executing th query; > ERROR: fmgr_info: function 1546856080: cache lookup failed > Here is the function code: >CREATE FUNCTION TRIGGER_UPDATE_AXPRDT_STATUS() >RETURNS OPAQUE >[snipped] >LANGUAGE 'plpgsql'; >CREATE TRIGGER TRIGGER_AXPRDT_FUTURESTATUS >BEFORE UPDATE ON AXPRDT >FOR EACH ROW >EXECUTE PROCEDURE TRIGGER_UPDATE_AXPRDT_STATUS(); The errors mean that after the trigger definition you maded a modification on the function rebuilding it. Try to delete the trigger and define it again. The fact that you are using "RETURN OPAQUE" instead of "RETURN TRIGGER" means that you are using a Postgres version prior then 7.3.x. Using the version 7.3.x ( I suggest you the 7.3.3 ) you can define that funcion: "CREATE OR REPLACE " so you are not forced to delete it before and your problem should gone. Gaetano ---(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] how to determine array size
>> I need to enumerate the constraints on any given column in a table, so >> I'm examining pg_constraint to get the relevant information. The >> conkey array contains a list of constrained columns, and although I am >> able to check conkey[1] for constraints on a single column, I would >> like to properly handle multi-column constraints. > >You may have an easier time dealing with pg_get_constraintdef() than >trying to get the info from the source. Is pg_get_constraintdef() documented somewhere? I'd like to know it's arguments, return format, and whether it will be supported in future postgres releases. From what I see in pg_dump.c, it appears to accept an oid from the pg_constraint table, and only work with foreign key constraints. True? That might be useful in some special-case code, but I really want a method that will work with all types of constraint. (The idea here is to enumerate all constraints on a table, along with their names, types, and constrained columns.) ---(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] how to determine array size
> Is pg_get_constraintdef() documented somewhere? I'd like to know it's > arguments, return format, and whether it will be supported in future > postgres releases. Support for it will improve, and it'll be around for a few releases anyway. > >From what I see in pg_dump.c, it appears to accept an oid from the > pg_constraint table, and only work with foreign key constraints. > True? That might be useful in some special-case code, but I really > want a method that will work with all types of constraint. (The idea The other types of constraints have been added in 7.4. In fact, it's the only way to fetch an accurate CHECK constraint in 7.4. Miscellaneous functions are documented at: http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-misc.html -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
