[SQL] function expression in FROM may not refer to other relations of same query level
Hello, I'm trying to use the ROWTYPE return value of a plpgsql function in a SELECT query. The test code is below. The following query is accepted: select id, usr, code, line1, line2 from tbl, get_lines(1); idusr code line1 line2 -- 1 one 1 A B 2 two 2 A B 3 three1 A B But the same query with a parameter returns an error: select id, usr, code, line1, line2 from tbl, get_lines(code); --> ERROR: function expression in FROM may not refer to other relations of same query level Is there another way to run this query and get: idusr code line1 line2 -- 1 one 1 A B 2 two 2 Z Z 3 three1 A B Thanks TEST CODE 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; 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); ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Stored procedures and "pseudo" fields
Lars Erik Thorsplass wrote: > On Tue, 20 Jul 2004 09:45:06 -0600, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > > Kinda like this: > > > > > > SELECT *, acl_check( objects.obid, ) AS mode FROM objects > > > WHERE mode > 0; > > > > Here's the problem. In order to do the select, the query first needs to > > run the where clause. I.e.: > > > > select a as test from table where a > 50; > > > > works, but > > > > select a as test from table where test > 50; > > > > fails. The reason is that when the where clause fires first, there IS > > no test yet, as it hasn't been materialized. what you need to do is: > > > > select custom_function(a,b) from table where custom_function(a,b) > 0; > > > > > > Thanks for clearing that up. I just hoped there was some magic I could > sprinkle on my query to get away from the extra overhead of running > the procedure twice :) > > Best regards.. > > L.E.Thorsplass > Pretty late, I know, but just for the record, AFAICS there is this magic as SELECT * FROM ( SELECT *, acl_check( objects.obid, ) AS mode FROM objects ) AS foo WHERE mode > 0; Regards, Christoph ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] sleep function
Is there a sleep function of some kind? I wanted to simulate a query taking a long time to execute for testing purposes. Thanks, John DeSoi, Ph.D. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sleep function
John DeSoi wrote: > Is there a sleep function of some kind? I wanted to simulate a query > taking a long time to execute for testing purposes. I can't think of one, no. I think you will have to use one of the server-side languages and call a sleep in there. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] function expression in FROM may not refer to other relations
Philippe Lang wrote: But the same query with a parameter returns an error: select id, usr, code, line1, line2 from tbl, get_lines(code); --> ERROR: function expression in FROM may not refer to other relations of same query level This is as expected and required -- you cannot refer to other FROM clause relations. I believe SQL99 defines a clause (LATERAL IIRC) that would allow this, but it hasn't been implemented yet. Is there another way to run this query and get: idusr code line1 line2 -- 1 one 1 A B 2 two 2 Z Z 3 three1 A B Whats wrong with just using CASE: select id, usr, code, case when code = 1 then 'A' else 'Z' end as line1, case when code = 1 then 'A' else 'Z' end as line2 from tbl; id | usr | code | line1 | line2 +---+--+---+--- 1 | one |1 | A | A 2 | two |2 | Z | Z 3 | three |1 | A | A (3 rows) Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] ERROR: Cross-database references are not implemented
Title: ERROR: Cross-database references are not implemented How do I explicidly create a cross-database reference? Mind you these databases are on the same server. In MSSQL you could do that through enterprise manager. Any help would be appreciated. __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
Re: [SQL] ERROR: Cross-database references are not implemented
Theo Galanakis wrote: How do I explicidly create a cross-database reference? Mind you these databases are on the same server. In MSSQL you could do that through enterprise manager. Any help would be appreciated. You cannot do cross database references per se, but you can use schemas, which roughly behave like individual databases do in MSSQL. See: http://www.postgresql.org/docs/7.4/static/ddl-schemas.html If you really need cross-database queries, see contrib/dblink (which would also work across servers), but it isn't as flexible as using schemas. HTH, Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] ERROR: Cross-database references are not implemented -
Title: RE: [SQL] ERROR: Cross-database references are not implemented - RESOLVED Thanks Joe. After I posted this message, I googled around until I realized when you mentioned below. Theo -Original Message- From: Joe Conway [mailto:[EMAIL PROTECTED]] Sent: Wednesday, 11 August 2004 1:35 PM To: Theo Galanakis Cc: [EMAIL PROTECTED] Subject: Re: [SQL] ERROR: Cross-database references are not implemented Theo Galanakis wrote: > How do I explicidly create a cross-database reference? > > Mind you these databases are on the same server. > > In MSSQL you could do that through enterprise manager. > > Any help would be appreciated. You cannot do cross database references per se, but you can use schemas, which roughly behave like individual databases do in MSSQL. See: http://www.postgresql.org/docs/7.4/static/ddl-schemas.html If you really need cross-database queries, see contrib/dblink (which would also work across servers), but it isn't as flexible as using schemas. HTH, Joe __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
[SQL] Wierded error in recursive function; debugging ideas?
Folks, I'm seeing this bizarre, unreproducable error in my logs: [2] Wrong datatype for second argument in call to in_array SQL: SELECT sf_event_decendants(66645,111) The problem is that it's proven completely impossible to reproduce this error at test time; it only seems to happen in production, and then only one out of about 200 times the function is called (although when the function errors it does seem to error 3-5 times in a row). sf_event_descendants is a recursive function which maps out which child events of the current event are visible to the current user. It does use arrays. I did try tinkering with some of the functions internals without apparent effect. I also checked for in_array and it's not a visible built-in function. Is this maybe a PostgreSQL bug? Version is 7.4.1 -- -Josh Berkus "A developer of Very Little Brain" Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Wierded error in recursive function; debugging ideas?
Josh Berkus wrote: I'm seeing this bizarre, unreproducable error in my logs: [2] Wrong datatype for second argument in call to in_array SQL: SELECT sf_event_decendants(66645,111) I also checked for in_array and it's not a visible built-in function. Is this maybe a PostgreSQL bug? Version is 7.4.1 There is no in_array() function in Postgres that I'm aware of -- you sure that isn't array_in()? The rest of that error message doesn't seem to be there in 7.4 either. Can we see the function? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] function expression in FROM may not refer to other relations of same query level
Hello, > Whats wrong with just using CASE: > > select id, usr, code, > case when code = 1 then 'A' else 'Z' end as line1, > case when code = 1 then 'A' else 'Z' end as line2 from tbl; The code I showed in my last mail was actually test code only. The logic is more complicated, and I'm not sure it's possible to implement it with a SELECT... CASE. What's more, the "get_lines" function already exists, and is already used in different parts of the database. I would like, if possible, to use it without changing it, or duplicating code. Philippe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
