Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id 11B099FB22B for ; Fri, 24 Nov 2006 00:21:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 98228-05 for ; Fri, 24 Nov 2006 00:21:35 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from web90512.mail.mud.yahoo.com (web90512.mail.mud.yahoo.com [216.252.100.179]) by postgresql.org (Postfix) with SMTP id 5647E9FB221 for ; Fri, 24 Nov 2006 00:21:35 -0400 (AST) Received: (qmail 55887 invoked by uid 60001); 24 Nov 2006 04:21:34 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=HC05677qwDLcV6VrywAY7PDyN8Qw4ftAI4kvRBILjE/l8uakwFMwrqn6Ln5t4V0tQESwoXyswbyykCJRgu1iiIi+ajd5FGLscaIB8uuNFSbthGdD+DmNUgEhxqBeFUS8SQW29PtYzcw6E/r8k0lCk+7GIYAmjkz/hK3vQay8IT8= ; Message-ID: <20061124042134.55885.qmail@web90512.mail.mud.yahoo.com> Received: from [219.95.167.205] by web90512.mail.mud.yahoo.com via HTTP; Thu, 23 Nov 2006 20:21:34 PST Date: Thu, 23 Nov 2006 20:21:34 -0800 (PST) From: Mulham freshcode Subject: Re: select into To: aklaver@comcast.net, pgsql-sql@postgresql.org Cc: Mulham freshcode , "A. Kretschmer" In-Reply-To: <200611231035.54710.aklaver@comcast.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1393164974-1164342094=:55097" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.665 tagged_above=0 required=5 tests=AWL, BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_MESSAGE, RCVD_IN_NJABL_PROXY X-Spam-Level: * X-Archive-Number: 200611/238 X-Sequence-Number: 26890 --0-1393164974-1164342094=:55097 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hello Adrian, Here is my execute string, sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' || sub_id ; execute sql_str1 into svc_data_rec ; svc_data_rec is a RECORD, which is supposed to be dynamic. If I need to define the structure of the record then there will be no point in using it since I need something to change according the structure the table, which i don't know in advance... Thank you, Mustafa... Adrian Klaver wrote: On Thursday 23 November 2006 09:19 am, Mulham freshcode wrote: > Hi, > Thanks very much for all the suggestions. Like Andreas said i have to > use EXECUTE to do this. That was my guess too but i was putting the INTO > cluase into the string before executing it. This is a step forward. The > problem now is getting the into to work with a record. Am using a RECORD > variable after declaring it in the INTO clause. pg is complaining > > ERROR: record "svc_data_rec" is not assigned yet > DETAIL: The tuple structure of a not-yet-assigned record is > indeterminate. CONTEXT: PL/pgSQL function "foo" line 130 at execute > statement > > I read in the docs the following, "The INTO clause specifies where the > results of a SELECT command should be assigned. If a row or variable list > is provided, it must exactly match the structure of the results produced by > the SELECT (when a record variable is used, it will configure itself to > match the result's structure automatically)." That i take to mean that the > above is ok. But it ain't. > > Thanks again for your guys help... > > "A. Kretschmer" wrote: am Wed, dem 22.11.2006, um 0:28:15 -0800 mailte Mulham freshcode folgendes: > > Hi guys, > > > > Am new to sql scripting so this might be a stupid question. Am getting > > an error while trying to do the following > > > > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id; > > > > where svc_data_rec is defined as record and svc_tbl_name is a varchar > > that holds the name of a table and sub_id is another varchar. the error > > message is > > You should rewrite your plpgsql-function. You can't handle with > string-vars in this way, you must create a string with your complete sql > and EXECUTE this string. > > Read > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPG >SQL-STATEMENTS-EXECUTING-DYN > > > Andreas My guess is that the error message is correct, the svc_data_rec has not had any values assigned to it. In other words the EXECUTE statement is not working the way you think it is. Could you post the EXECUTE string? -- Adrian Klaver aklaver@comcast.net --------------------------------- Want to start your own business? Learn how on Yahoo! Small Business. --0-1393164974-1164342094=:55097 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hello Adrian,

Here is my execute string,

sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' || sub_id ;
execute sql_str1 into svc_data_rec ;

svc_data_rec is a RECORD, which is supposed to be dynamic. If I need to define the structure of the record then there will be no point in using it since I need something to change according the structure the table, which i don't know in advance...

Thank you,

Mustafa...


Adrian Klaver <aklaver@comcast.net> wrote:
On Thursday 23 November 2006 09:19 am, Mulham freshcode wrote:
> Hi,
> Thanks very much for all the suggestions. Like Andreas said i have to
> use EXECUTE to do this. That was my guess too but i was putting the INTO
> cluase into the string before executing it. This is a step forward. The
> problem now is getting the into to work with a record. Am using a RECORD
> variable after declaring it in the INTO clause. pg is complaining
>
> ERROR: record "svc_data_rec" is not assigned yet
> DETAIL: The tuple structure of a not-yet-assigned record is
> indeterminate. CONTEXT: PL/pgSQL function "foo" line 130 at execute
> statement
>
> I read in the docs the following, "The INTO clause specifies where the
> results of a SELECT command should be assigned. If a row or variable list
> is provided, it must exactly match the structure of the results produced by
> the SELECT (when a record variable is used, it will configure itself to
> match the result's structure automatically)." That i take to mean that the
> above is ok. But it ain't.
>
> Thanks again for your guys help...
>
> "A. Kretschmer" wrote: am Wed, dem
22.11.2006, um 0:28:15 -0800 mailte Mulham freshcode folgendes:
> > Hi guys,
> >
> > Am new to sql scripting so this might be a stupid question. Am getting
> > an error while trying to do the following
> >
> > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
> >
> > where svc_data_rec is defined as record and svc_tbl_name is a varchar
> > that holds the name of a table and sub_id is another varchar. the error
> > message is
>
> You should rewrite your plpgsql-function. You can't handle with
> string-vars in this way, you must create a string with your complete sql
> and EXECUTE this string.
>
> Read
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPG
>SQL-STATEMENTS-EXECUTING-DYN
>
>
> Andreas

My guess is that the error message is correct, the svc_data_rec has not had
any values assigned to it. In other words the EXECUTE statement is not
working the way you think it is. Could you post the EXECUTE string?

--
Adrian Klaver
aklaver@comcast.net

Want to start your own business? Learn how on Yahoo! Small Business. --0-1393164974-1164342094=:55097--