[SQL] EXECUTE SELECT INTO... last one!
PgSql8.1beta2 gives me this: ERROR: EXECUTE of SELECT ... INTO is not implemented yet CONTEXT: PL/pgSQL function "_rndbroj" line 5 at execute statement Shall I forget about this once and for all (and use FOR construct), or there is a chance that will be 'fixed'? Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(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] EXECUTE SELECT INTO... last one!
On Fri, Sep 30, 2005 at 10:00:35AM +0200, Mario Splivalo wrote:
> PgSql8.1beta2 gives me this:
>
> ERROR: EXECUTE of SELECT ... INTO is not implemented yet
> CONTEXT: PL/pgSQL function "_rndbroj" line 5 at execute statement
Could you post a simple example of what you're trying to do? The
following works for me in 8.1beta2 (from CVS):
CREATE FUNCTION mycount(text) RETURNS bigint AS $$
DECLARE
query text;
cntbigint;
BEGIN
query := 'SELECT count(*) FROM ' || quote_ident($1);
EXECUTE query INTO cnt;
RETURN cnt;
END;
$$ LANGUAGE plpgsql;
SELECT mycount('pg_class');
mycount
-
188
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] EXECUTE SELECT INTO... last one!
On Fri, 2005-09-30 at 02:34 -0600, Michael Fuhr wrote: > On Fri, Sep 30, 2005 at 10:00:35AM +0200, Mario Splivalo wrote: > > PgSql8.1beta2 gives me this: > > > > ERROR: EXECUTE of SELECT ... INTO is not implemented yet > > CONTEXT: PL/pgSQL function "_rndbroj" line 5 at execute statement > > Could you post a simple example of what you're trying to do? The > following works for me in 8.1beta2 (from CVS): > > CREATE FUNCTION mycount(text) RETURNS bigint AS $$ > DECLARE > query text; > cntbigint; > BEGIN > query := 'SELECT count(*) FROM ' || quote_ident($1); > EXECUTE query INTO cnt; > RETURN cnt; > END; > $$ LANGUAGE plpgsql; Yes, it's my fault. I was testing it on pg7.4. On 8.1beta2 works as you explained. Though it would be realy nice to be able to do var := col FROM tempTable WHERE condition withouth EXECUTE'ing and stuff. Sorry for the fuss I might have created. Mario -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(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
[SQL] combination of function to simple query makes query slow
Hi,
I'm having some problems in combining a function within a standard
query.
I basically have two tables (see between hashes below):
* alias_table: objects can have different names (e.g. name1 is an alias
of name2, and name3 is an alias of name4)
* mapping_table: shows objects that 'lead to' other objects (e.g. the
object with name1 leads to the object with name5)
I want to traverse the mapping_table in a transparent way: if a first
object leads to a second object, and that second object leads to a third
object, I want to list the first and the third objects. So from the
example table below: name6 leads to name2, which is an alias of name1,
which in its turn leads to name5. The result of my query should be:
name6 leads to name5.
To accomplish this, I wrote a little function ('aliases_of'), as
described below between the hashes.
The query
SELECT aliases_of(1);
returns:
aliases_of
--
1
2
, which is the expected output.
The query
SELECT * FROM mapping_table
WHERE object1_id IN ( SELECT aliases_of(2) );
returns:
object1_id | object1_name | object2_id | object2_name
-
1 | name1| 5 | name5
, which is the expected output, because it is equivalent to:
SELECT * FROM mapping_table
WHERE object1_id IN ( 1, 2 );
However, the query that would traverse the data:
SELECT m1.object1_id, m1.object1_name, m2.object2_id, m2.object2_name
FROM mapping_table m1, mapping_table m2
WHERE m1.object2_id IN ( SELECT aliases_of(m2.object1_id) );
just keeps on running, and never seems to stop. (I must say that that
table in effect contains about a million records.)
The expected output would have been:
object1_id | object1_name | object2_id | object2_name
-
6 | name6| 5 | name5
I also tried to following, which doesn't work either.
SELECT m1.object1_id, m1.object1_name, m2.object2_id, m2.object2_name
FROM mapping_table m1 JOIN mapping_table m2 ON ( m1.object2_id IN (
SELECT aliases_of(m2.object1_id) );
Is there a way to speed up this query? I'd also have to be able to
traverse the mapping_table ever further, following a path of things that
'lead to' other things.
I hope I made myself clear a bit...
Any help appreciated. Many thanks,
jan.
(A) TABLE: alias_table
object1_id | object1_name | object2_id | object2_name
-
1 | name1| 2 | name2
3 | name3| 4 | name4
(B) TABLE: mapping_table
object1_id | object1_name | object2_id | object2_name
-
1 | name1| 5 | name5
6 | name6| 2 | name2
(C) FUNCTION: aliases_of
CREATE FUNCTION aliases_of(bigint) RETURNS SETOF bigint
AS 'SELECT $1
UNION
SELECT object1_id FROM alias_table WHERE object2_id = $1
UNION
SELECT object2_id FROM alias_table WHERE object1_id = $1
'
LANGUAGE SQL;
-The obligatory disclaimer
The information contained in this e-mail (including any attachments) is
confidential and is intended for the use of the addressee only. The
opinions expressed within this e-mail (including any attachments) are
the opinions of the sender and do not necessarily constitute those of
Roslin Institute (Edinburgh) ("the Institute") unless specifically
stated by a sender who is duly authorised to do so on behalf of the
Institute.
---(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] combination of function to simple query makes query slow
On 9/30/05, jan aerts (RI) <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'm having some problems in combining a function within a standard
> query.
>
> I basically have two tables (see between hashes below):
> * alias_table: objects can have different names (e.g. name1 is an alias
> of name2, and name3 is an alias of name4)
> * mapping_table: shows objects that 'lead to' other objects (e.g. the
> object with name1 leads to the object with name5)
>
> I want to traverse the mapping_table in a transparent way: if a first
> object leads to a second object, and that second object leads to a third
> object, I want to list the first and the third objects. So from the
> example table below: name6 leads to name2, which is an alias of name1,
> which in its turn leads to name5. The result of my query should be:
> name6 leads to name5.
>
> To accomplish this, I wrote a little function ('aliases_of'), as
> described below between the hashes.
>
> The query
> SELECT aliases_of(1);
> returns:
> aliases_of
> --
> 1
> 2
> , which is the expected output.
>
> The query
> SELECT * FROM mapping_table
> WHERE object1_id IN ( SELECT aliases_of(2) );
> returns:
> object1_id | object1_name | object2_id | object2_name
> -
> 1 | name1| 5 | name5
> , which is the expected output, because it is equivalent to:
> SELECT * FROM mapping_table
> WHERE object1_id IN ( 1, 2 );
>
> However, the query that would traverse the data:
> SELECT m1.object1_id, m1.object1_name, m2.object2_id, m2.object2_name
> FROM mapping_table m1, mapping_table m2
> WHERE m1.object2_id IN ( SELECT aliases_of(m2.object1_id) );
> just keeps on running, and never seems to stop. (I must say that that
> table in effect contains about a million records.)
> The expected output would have been:
> object1_id | object1_name | object2_id | object2_name
> -
> 6 | name6| 5 | name5
>
> I also tried to following, which doesn't work either.
> SELECT m1.object1_id, m1.object1_name, m2.object2_id, m2.object2_name
> FROM mapping_table m1 JOIN mapping_table m2 ON ( m1.object2_id IN (
> SELECT aliases_of(m2.object1_id) );
>
> Is there a way to speed up this query? I'd also have to be able to
> traverse the mapping_table ever further, following a path of things that
> 'lead to' other things.
>
> I hope I made myself clear a bit...
> Any help appreciated. Many thanks,
> jan.
>
>
> (A) TABLE: alias_table
> object1_id | object1_name | object2_id | object2_name
> -
> 1 | name1| 2 | name2
> 3 | name3| 4 | name4
>
> (B) TABLE: mapping_table
> object1_id | object1_name | object2_id | object2_name
> -
> 1 | name1| 5 | name5
> 6 | name6| 2 | name2
>
> (C) FUNCTION: aliases_of
> CREATE FUNCTION aliases_of(bigint) RETURNS SETOF bigint
> AS 'SELECT $1
> UNION
> SELECT object1_id FROM alias_table WHERE object2_id = $1
> UNION
> SELECT object2_id FROM alias_table WHERE object1_id = $1
> '
> LANGUAGE SQL;
>
>
>
maybe you can show us an EXPLAIN of your select:
EXPLAIN select_statement
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---(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] EXECUTE with SELECT INTO variable, or alternatives
Per the docs: "The results from SELECT commands are discarded by EXECUTE, and SELECT INTO is not currently supported within EXECUTE. So there is no way to extract a result from a dynamically-created SELECT using the plain EXECUTE command. There are two other ways to do it, however: one is to use the FOR-IN-EXECUTE loop form described in Section 35.7.4, and the other is to use a cursor with OPEN-FOR-EXECUTE, as described in Section 35.8.2." http://www.postgresql.org/docs/8.0/static/plpgsql- statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN So you've already hit upon one of your options. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) On Sep 29, 2005, at 1:16 PM, Mario Splivalo wrote: I can assign a value to a variable in several ways: myVar := (SELECT col FROM table WHERE somecondition...) myVar := col FROM table WHERE somecondtition... SELECT col INTO myVar FROM table WHERE somecondition How do I do any of the above using EXECUTE? I need to be able to assign the value to a variable, a value returned by a querry on a temporary table. So far I have found workaround like this: myRec record; FOR rec IN EXECUTE ''SELECT col FROM table WHERE somecondition'' LOOP myVar := rec END LOOP Obviously, the above SELECT query returns only one row. Still, if is realy inconvinient to have FOR...LOOP...END LOOP construct for assigning the value to a variable 'read' from the temporary table. Mario ---(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] EXECUTE with SELECT INTO variable, or alternatives
On Fri, Sep 30, 2005 at 03:59:06PM -0500, Thomas F. O'Connell wrote: > Per the docs: > > "The results from SELECT commands are discarded by EXECUTE, and > SELECT INTO is not currently supported within EXECUTE. So there is no > way to extract a result from a dynamically-created SELECT using the > plain EXECUTE command. There are two other ways to do it, however: > one is to use the FOR-IN-EXECUTE loop form described in Section > 35.7.4, and the other is to use a cursor with OPEN-FOR-EXECUTE, as > described in Section 35.8.2." 8.1 will have EXECUTE ... INTO. http://developer.postgresql.org/docs/postgres/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
