Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 37A329FA374 for ; Thu, 23 Nov 2006 13:19:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 28623-05 for ; Thu, 23 Nov 2006 13:19:11 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from web90510.mail.mud.yahoo.com (web90510.mail.mud.yahoo.com [216.252.100.177]) by postgresql.org (Postfix) with SMTP id 761669FA372 for ; Thu, 23 Nov 2006 13:19:10 -0400 (AST) Received: (qmail 89662 invoked by uid 60001); 23 Nov 2006 17:19:09 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=gQbqe7QCN2vwnMe1Bp3j0z3c5lN7z906W+snY3ewfKCUlrvypFbjCZuWSq7TP8ulbfGPrhc02THXswpA4bkvOgv4F+NAtN5BKL0OmnA9lT8Q4/z8PFqB7gjidecm/oqJx4CmwSWGUXwvaq56oCw6ek8Db43YNzlqWOGDleX1azQ= ; Message-ID: <20061123171909.89660.qmail@web90510.mail.mud.yahoo.com> Received: from [60.48.99.212] by web90510.mail.mud.yahoo.com via HTTP; Thu, 23 Nov 2006 09:19:09 PST Date: Thu, 23 Nov 2006 09:19:09 -0800 (PST) From: Mulham freshcode Subject: Re: select into To: "A. Kretschmer" , pgsql-sql@postgresql.org In-Reply-To: <20061122154742.GA28962@a-kretschmer.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-189034547-1164302349=:89225" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.108 tagged_above=0 required=5 tests=BAYES_00, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS, HTML_10_20, HTML_MESSAGE X-Spam-Level: ** X-Archive-Number: 200611/236 X-Sequence-Number: 26888 --0-189034547-1164302349=:89225 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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#PLPGSQL-STATEMENTS-EXECUTING-DYN Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly --------------------------------- Access over 1 million songs - Yahoo! Music Unlimited. --0-189034547-1164302349=:89225 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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" <andreas.kretschmer@schollglas.com> 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#PLPGSQL-STATEMENTS-EXECUTING-DYN


Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Access over 1 million songs - Yahoo! Music Unlimited. --0-189034547-1164302349=:89225--