From jim@luckydigital.com Sun Feb  23 22:07:51 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 0383947580B
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 17:07:51 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 28901-08 for <pgsql-sql@postgresql.org>;
	Sun, 23 Feb 2003 17:07:40 -0500 (EST)
Received: from luckydigital.com (www.luckydigital.com [203.109.157.4])
	by postgresql.org (Postfix) with ESMTP id 9D1B1474E53
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 17:07:39 -0500 (EST)
Received: from jax (203-109-198-1.akl.ihugultra.co.nz [203.109.198.1])
	by luckydigital.com (8.11.6/8.10.0) with SMTP id h1NM7eC14410
	for <pgsql-sql@postgresql.org>; Mon, 24 Feb 2003 11:07:40 +1300
Message-ID: <006401c2db87$fc09be00$c900a8c0@jax>
From: "James Cooper" <jim@luckydigital.com>
To: "sql" <pgsql-sql@postgresql.org>
Subject: syntax question
Date: Mon, 24 Feb 2003 11:07:41 +1300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0061_01C2DBF4.F2C5D5F0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/516
X-Sequence-Number: 11914

This is a multi-part message in MIME format.

------=_NextPart_000_0061_01C2DBF4.F2C5D5F0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

FOR total IN EXECUTE subquery LOOP

END LOOP;

RETURN total.tot;



I have a function that returns a total from a dynamic query I use to genera=
te the total query

I've tried to modify it as a loop to get a single row value is unnecessary =
but I'm failing on my syntax

I've tried=20

select into total subquery=20

-but it wont work(when I attempt this I remove the select from the beginnin=
g of my sub query)

can someone show me the syntax to execute a select into with a string that =
contains a query in it?



James

------=_NextPart_000_0061_01C2DBF4.F2C5D5F0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1141" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2><FONT size=3D1>
<P>FOR total IN EXECUTE subquery LOOP</P>
<P></FONT><FONT color=3D#008000 size=3D1>END</FONT><FONT size=3D1> LOOP;</P>
<P>RETURN total.tot;</P>
<P>&nbsp;</P>
<P><FONT size=3D2>I have a function that returns a total from a dynamic que=
ry I=20
use to generate the total query</FONT></P>
<P><FONT size=3D2>I've tried to modify it as a loop to get a single row val=
ue is=20
unnecessary but I'm failing on my syntax</FONT></P>
<P><FONT size=3D2>I've tried </FONT></P>
<P><FONT size=3D2>select into total subquery </FONT></P>
<P><FONT size=3D2>-but it wont work(when I attempt this I remove the select=
 from=20
the beginning of my sub query)</FONT></P>
<P><FONT size=3D2>can someone show me the syntax to execute a select into w=
ith a=20
string that contains a query in it?</FONT></P>
<P><FONT size=3D2></FONT>&nbsp;</P>
<P><FONT size=3D2>James</FONT></P></FONT></FONT></DIV></BODY></HTML>

------=_NextPart_000_0061_01C2DBF4.F2C5D5F0--




From josh@agliodbs.com Sun Feb  23 22:54:25 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id B58094758F1
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 17:54:25 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 36037-05 for <pgsql-sql@postgresql.org>;
	Sun, 23 Feb 2003 17:54:14 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 4BD92474E5C
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 17:52:44 -0500 (EST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2853918; Sun, 23 Feb 2003 14:52:45 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: "James Cooper" <jim@luckydigital.com>,
	"sql" <pgsql-sql@postgresql.org>
Subject: Re: syntax question
Date: Sun, 23 Feb 2003 14:52:00 -0800
User-Agent: KMail/1.4.3
References: <006401c2db87$fc09be00$c900a8c0@jax>
In-Reply-To: <006401c2db87$fc09be00$c900a8c0@jax>
MIME-Version: 1.0
Message-Id: <200302231452.00484.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/517
X-Sequence-Number: 11915

James,

> FOR total IN EXECUTE subquery LOOP
>
> END LOOP;
>
> RETURN total.tot;

The above is fine, except that you need to assign total.tot to a variable=
=20
*inside* the loop:

FOR total IN EXECUTE subquery LOOP
	return_total :=3D total.tot;
END LOOP;

RETURN return_total;

This is because the expression "total.tot" is out of scope as soon as the l=
oop=20
exits.

--=20
Josh Berkus
Aglio Database Solutions
San Francisco



From josh@agliodbs.com Sun Feb  23 23:08:18 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id A776C4758BD
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 18:08:18 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 36933-06 for <pgsql-sql@postgresql.org>;
	Sun, 23 Feb 2003 18:08:07 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id AFB3B474E5C
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 18:08:07 -0500 (EST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2853939; Sun, 23 Feb 2003 15:08:08 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: "James Cooper" <jim@luckydigital.com>
Subject: Re: syntax question
Date: Sun, 23 Feb 2003 15:07:23 -0800
User-Agent: KMail/1.4.3
References: <006401c2db87$fc09be00$c900a8c0@jax>
	<200302231452.00484.josh@agliodbs.com>
	<007701c2db8f$b22b3ea0$c900a8c0@jax>
In-Reply-To: <007701c2db8f$b22b3ea0$c900a8c0@jax>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Message-Id: <200302231507.23831.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/519
X-Sequence-Number: 11917

James,

> I understand this and my current example actually works.
> My question is I dont need a loop as it only ever returns one row.

This is a current limitation of PL/pgSQL.   Until PL/pgSQL is improved by s=
ome=20
enterprising soul, you cannot select the results of a query directly into a=
=20
RECORD variable without a loop.

For that matter, you will find that you cannot declare an array variable=20
inside a PL/pgSQL procedure.  This is also on the TODO list.

--=20
Josh Berkus
Aglio Database Solutions
San Francisco



From josh@agliodbs.com Mon Feb  24 00:26:21 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 1DD7B474E53
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 19:26:21 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 49042-09 for <pgsql-sql@postgresql.org>;
	Sun, 23 Feb 2003 19:26:10 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 5EC274758DC
	for <pgsql-sql@postgresql.org>; Sun, 23 Feb 2003 19:26:10 -0500 (EST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2854003; Sun, 23 Feb 2003 16:26:12 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: "James Cooper" <jim@luckydigital.com>
Subject: Re: syntax question
Date: Sun, 23 Feb 2003 16:25:26 -0800
User-Agent: KMail/1.4.3
References: <006401c2db87$fc09be00$c900a8c0@jax>
	<200302231507.23831.josh@agliodbs.com>
	<009a01c2db95$15173e10$c900a8c0@jax>
In-Reply-To: <009a01c2db95$15173e10$c900a8c0@jax>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Message-Id: <200302231625.26793.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/522
X-Sequence-Number: 11920

James,

> but thats what:
> rec record
> select into rec id from table;
> return rec.id
>
> does
>
> my question was can i do this with a query built inside a string?

No.  That's what I was talking about.  You have to use the loop.


--=20
Josh Berkus
Aglio Database Solutions
San Francisco



From jeff_eckermann@yahoo.com Mon Feb  24 15:19:14 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 78165475ADE
	for <pgsql-sql@postgresql.org>; Mon, 24 Feb 2003 10:19:14 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 84732-05 for <pgsql-sql@postgresql.org>;
	Mon, 24 Feb 2003 10:19:03 -0500 (EST)
Received: from web20805.mail.yahoo.com (web20805.mail.yahoo.com
	[216.136.226.194])
	by postgresql.org (Postfix) with SMTP id 4038E4759BD
	for <pgsql-sql@postgresql.org>; Mon, 24 Feb 2003 10:17:37 -0500 (EST)
Message-ID: <20030224151740.15417.qmail@web20805.mail.yahoo.com>
Received: from [208.13.35.65] by web20805.mail.yahoo.com via HTTP;
	Mon, 24 Feb 2003 07:17:40 PST
Date: Mon, 24 Feb 2003 07:17:40 -0800 (PST)
From: Jeff Eckermann <jeff_eckermann@yahoo.com>
Subject: Re: syntax question
To: Josh Berkus <josh@agliodbs.com>,
	James Cooper <jim@luckydigital.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200302231507.23831.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/530
X-Sequence-Number: 11928

--- Josh Berkus <josh@agliodbs.com> wrote:
>  you cannot select the results of
> a query directly into a 
> RECORD variable without a loop.
> 

Pardon??

kper=# create function test(integer) returns varchar
as 'declare stuff record; begin select * into stuff
from employee where empid =$1; return stuff.empnum;
end;' language 'plpgsql';
CREATE

kper=# select test(66664);
 test 
------
 3094
(1 row)

kper=# select empid, empnum from employee where empid
= 66664;
 empid | empnum 
-------+--------
 66664 | 3094
(1 row)

This functinality has been in pl/pgsql from early days
AFAIK.  Be aware that this only works reliably if you
have some way to be sure of what will be selected
("empid" is a unique column in this example), because
the second and later rows to be returned from the
query will be silently discarded.

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/



From tgl@sss.pgh.pa.us Mon Feb  24 15:33:53 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 87F93475AE5
	for <pgsql-sql@postgresql.org>; Mon, 24 Feb 2003 10:33:53 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 87555-06 for <pgsql-sql@postgresql.org>;
	Mon, 24 Feb 2003 10:33:43 -0500 (EST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by postgresql.org (Postfix) with ESMTP id AA2F847580B
	for <pgsql-sql@postgresql.org>; Mon, 24 Feb 2003 10:30:24 -0500 (EST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h1OFUE5u003493;
	Mon, 24 Feb 2003 10:30:14 -0500 (EST)
To: Jeff Eckermann <jeff_eckermann@yahoo.com>
Cc: Josh Berkus <josh@agliodbs.com>,
	James Cooper <jim@luckydigital.com>, pgsql-sql@postgresql.org
Subject: Re: syntax question 
In-reply-to: <20030224151740.15417.qmail@web20805.mail.yahoo.com> 
References: <20030224151740.15417.qmail@web20805.mail.yahoo.com>
Comments: In-reply-to Jeff Eckermann <jeff_eckermann@yahoo.com>
	message dated "Mon, 24 Feb 2003 07:17:40 -0800"
Date: Mon, 24 Feb 2003 10:30:14 -0500
Message-ID: <3492.1046100614@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/531
X-Sequence-Number: 11929

Jeff Eckermann <jeff_eckermann@yahoo.com> writes:
> --- Josh Berkus <josh@agliodbs.com> wrote:
>> you cannot select the results of
>> a query directly into a 
>> RECORD variable without a loop.

> Pardon??

I think Josh meant to say you can't select the results of a *dynamically
constructed* query without a loop --- that is, you need FOR ... EXECUTE.
A plain EXECUTE doesn't support plpgsql's notion of SELECT INTO.

			regards, tom lane



From josh@agliodbs.com Mon Feb  24 18:49:37 2003
X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 07FB5475E10
	for <pgsql-sql@postgresql.org>; Mon, 24 Feb 2003 13:49:37 -0500 (EST)
Received: from postgresql.org ([64.49.215.8])
	by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with
	SMTP id 34554-06 for <pgsql-sql@postgresql.org>;
	Mon, 24 Feb 2003 13:49:26 -0500 (EST)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by postgresql.org (Postfix) with ESMTP id 04629474E4F
	for <pgsql-sql@postgresql.org>; Mon, 24 Feb 2003 13:22:55 -0500 (EST)
Received: from [63.195.55.98] (HELO spooky)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 2854952; Mon, 24 Feb 2003 10:22:53 -0800
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Tom Lane <tgl@sss.pgh.pa.us>,
	Jeff Eckermann <jeff_eckermann@yahoo.com>
Subject: Re: syntax question
Date: Mon, 24 Feb 2003 10:22:02 -0800
User-Agent: KMail/1.4.3
Cc: James Cooper <jim@luckydigital.com>, pgsql-sql@postgresql.org
References: <20030224151740.15417.qmail@web20805.mail.yahoo.com>
	<3492.1046100614@sss.pgh.pa.us>
In-Reply-To: <3492.1046100614@sss.pgh.pa.us>
MIME-Version: 1.0
Message-Id: <200302241022.02853.josh@agliodbs.com>
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new
X-Archive-Number: 200302/534
X-Sequence-Number: 11932

Jeff,

> I think Josh meant to say you can't select the results of a *dynamically
> constructed* query without a loop --- that is, you need FOR ... EXECUTE.
> A plain EXECUTE doesn't support plpgsql's notion of SELECT INTO.

That's correct.  See the rest of the thread.

--=20
Josh Berkus
Aglio Database Solutions
San Francisco