Re: [SQL] function expression in FROM may not refer to other relations of same query level
Thanks a lot for your support. With a subselect and offset 0, the function is called only once per row, that's fine. Here is the final test code, in case it can help anyone. --- CREATE TYPE public.lines AS ( line1 varchar(10), line2 varchar(10) ); CREATE TABLE public.tbl ( id int4 PRIMARY KEY, usr varchar(10), code int4 ) WITHOUT OIDS; CREATE FUNCTION public.get_lines(int4) RETURNS lines AS ' DECLARE code ALIAS FOR $1; lines lines%rowtype; BEGIN IF code = 1 THEN lines.line1 = ''A''; lines.line2 = ''B''; ELSE lines.line1 = ''Z''; lines.line2 = ''Z''; END IF; RAISE NOTICE ''---> get_lines was called...''; RETURN lines; END; ' LANGUAGE 'plpgsql' VOLATILE; INSERT INTO tbl VALUES (1, 'one', 1); INSERT INTO tbl VALUES (2, 'two', 2); INSERT INTO tbl VALUES (3, 'three', 1); INSERT INTO tbl VALUES (4, 'four', 2); select id, usr, code, (get_lines_data).line1, (get_lines_data).line2 from ( select id, usr, code, get_lines(code) as get_lines_data from tbl offset 0 ) as ss; --- Philippe Lang -Message d'origine- De : Tom Lane [mailto:[EMAIL PROTECTED] Envoyé : jeudi, 12. août 2004 16:31 À : Philippe Lang Cc : [EMAIL PROTECTED] Objet : Re: [SQL] function expression in FROM may not refer to other relations of same query level "Philippe Lang" <[EMAIL PROTECTED]> writes: > I wish there was a way to run the query like this: > select > id, > usr, > code, > CAST(get_lines(code) as lines) > from tbl; You can do something like this: regression=# create type complex as (r float8, i float8); CREATE TYPE regression=# create function fooey(float8) returns complex as regression-# 'select $1 + 1, $1 + 2' language sql; CREATE FUNCTION regression=# select f1, (fooey).r, (fooey).i from regression-# (select f1, fooey(f1) as fooey from float8_tbl) ss; f1 | r | i ---+---+--- 0 | 1 | 2 -34.84 |-33.84 |-32.84 -1004.3 | -1003.3 | -1002.3 -1.2345678901234e+200 | -1.2345678901234e+200 | -1.2345678901234e+200 -1.2345678901234e-200 | 1 | 2 (5 rows) Note the odd-looking parenthesization --- you can't write just "fooey.r" because that looks like it should be a table and field name, not a field name that is selected from. If the sub-select is too simple, as it is in this case, the planner is likely to "flatten out" the query into select f1, (fooey(f1)).r, (fooey(f1)).i from float8_tbl; thus defeating your purpose of not calling the function twice. The currently best hack for preventing this is to add "OFFSET 0" to the sub-select: select f1, (fooey).r, (fooey).i from (select f1, fooey(f1) as fooey from float8_tbl offset 0) ss; regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Wierd Error on update
> Original 7.3 release, or (I hope) 7.3.something? Red Hat 7.3.2.96-113 Linux Version 2.4.20-20.7custom from cat /proc/version > > I am getting two errors which are a bit confounding. > > ERROR: pg_class_aclcheck:relation 474653086 not found > > Are there any views involved? No views. Is the statement invoking any functions? The statement is not invoking any fuctions However heres the statements, UPDATE school_info_l SET ind_default_data = 't'; UPDATE school_info_l SET sis_system_id = 9 where school_id IN (492, 434, 436); which are amazingly simple. Both queries return the same exact error, same error. not your > How about triggers? Foreign keys? Rules? Neither of the updates touch any rules. sis_system_id has a foreign key constaint to another table that does in it contain a value of 9 for sis_system_id in that table, but the first isnt touching anything that has a trigger or foreign key constraint attached to the column. The table school_info_l has many contraints that affect other tables however > Also, is it always the same number in the error, or does that change > from run to run? The number is the same for each time I run the update file on the same database. with this two queries having the same error. Same result to, if I run these two after the first update file finishes there is no problem, but if I try to run it at the end of hte first file, choke. Very Odd problem to me, since the update statemenets are so simple. -Tom ---(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] Wierd Error on update
Thomas Seeber <[EMAIL PROTECTED]> writes: >>> I am getting two errors which are a bit confounding. >>> ERROR: pg_class_aclcheck:relation 474653086 not found >> How about triggers? Foreign keys? Rules? > Neither of the updates touch any rules. > sis_system_id has a foreign key constaint to another table that does > in it contain a value of 9 for sis_system_id in that table, but the > first isnt touching anything that has a trigger or foreign key > constraint attached to the column. > The table school_info_l has many contraints that affect other tables however I would guess that one or another of these foreign-key constraints is broken. I'd suggest looking through pg_trigger to see if there are any entries with tgconstrrelid = 474653086. I wouldn't necessarily recommend just deleting such an entry, mind you, but finding where the problem is is the first step... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [PERFORM] Performance Problem With Postgresql!
Arash, > We are having a performance problem with our database. The problem > exists when we include a constraint in GCTBALLOT. The constraint is as > follows: You posted twice, to three different mailing lists each time. This is discourteous. Please do not do so again, as people may not help you if they feel you are being rude. Richard H has posted the solution to your problem. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
