Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TAQsY-0003vr-2T for pgsql-sql@postgresql.org; Sat, 08 Sep 2012 19:39:42 +0000 Received: from mail-pb0-f46.google.com ([209.85.160.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TAQsU-0002FK-Rs for pgsql-sql@postgresql.org; Sat, 08 Sep 2012 19:39:40 +0000 Received: by pbbrr13 with SMTP id rr13so1142159pbb.19 for ; Sat, 08 Sep 2012 12:39:37 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=user-agent:date:subject:from:to:message-id:thread-topic :mime-version:content-type:x-gm-message-state; bh=uBwiIAxH4KrkFBzCYkiTRTi8jXFe4PbL49IxKnnki6k=; b=LG0m1bkRyH3L7LmhfpvV2xAzbLGluXWia9G9L3s7Iw6CE7ppVmx6M59ZTNIUHkYo9t Yx/bsZqZS1BgMnN6kMDM4cEKjOPJga6m9rIS+tbQhdHRAc9Cjqr45IVS70XKy0KQz3uh YLr4coCJt1/jHGUSf9JN/XZG355V27aAX6XSAXsiKTrHc2zu/fPJ6EZsB25S7vrNq2qD yquSR26UQLFeqQAvGwFsQFN5pc2aA0n9/gYiHdjK/cmlHUsd5FDaDH9j4+KatMQ6PQx9 NQQaRAE7lyV7Omuu4ICpuPuOixSF0QujrSBOGNV3nxllgdOO+EyOtLIJEKe5VGakECS+ 19Ug== Received: by 10.68.212.70 with SMTP id ni6mr17143475pbc.22.1347133177437; Sat, 08 Sep 2012 12:39:37 -0700 (PDT) Received: from [192.168.1.22] (adsl-074-245-040-156.sip.clt.bellsouth.net. [74.245.40.156]) by mx.google.com with ESMTPS id or1sm5817452pbb.10.2012.09.08.12.39.34 (version=SSLv3 cipher=OTHER); Sat, 08 Sep 2012 12:39:36 -0700 (PDT) User-Agent: Microsoft-MacOutlook/14.2.3.120616 Date: Sat, 08 Sep 2012 15:39:30 -0400 Subject: returning values from dynamic SQL to a variable From: James Sharrett To: Message-ID: Thread-Topic: returning values from dynamic SQL to a variable Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3429963574_9027228" X-Gm-Message-State: ALoCoQnuDiluaV3z4tONMF85O9RdoClFZrD33r7N2eDQq/0JQTe6MaZlWg2YkaTQrbCZrKvijwla X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201209/17 X-Sequence-Number: 36819 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3429963574_9027228 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit I have a PG function ( using plpgsql) that calls a number of sub functions also in plpgsql. I have two main problems that seem to be variations on the same theme of running dynamic SQL from a variable with the EXECUTE statement and returning the results back to a variable defined in the calling function. Problem 1: return the results of a table query to a variable. I have a logging table that my sub functions write to. At the beginning of my main function I want to read a run number from the logging table and increment it by one to then pass into my sub functions. I've properly declared the variable (v_runnumber) and the data type is correct. The following statement works fine in the main function and stores the value in the variable. select max(runnumber) into v_runnumber from MySchema.log_table; However, MySchema is a parameter that gets passed into the main function because I need this to work for multiple schemas. If I try and make this dynamic by using the following statement: Sql := 'select max(run number) into v_runnumber from ' || MySchema || '.log_table;'; Execute Sql; I get the following error message (even though the resulting value in the text variable Sql is valid code): ERROR: query string argument of EXECUTE is null SQL state: 22004 Problem 2: returning the results of a function call to a variable. This is a similar issue to #1 but in this case, I'm calling a function from the main function and trying to get the return value back (a single integer) from the sub function to test for errors. Again, I'm calling the function with dynamical SQL because of the need to take user values from the main function to call the sub functions. The function call: sql := 'select * from public.elt_set_locking(1,' || quote_literal(tenant) || ',' || quote_literal(app) || ',' || quote_literal(cycle) || ',' || v_runnumber || ');'; execute sql; Works fine. However when I try and store the value coming back from the function into a main variable with the following call I get an error: sql := 'select * into v_retcode from public.elt_set_locking(1,' || quote_literal(tenant) || ',' || quote_literal(app) || ',' || quote_literal(cycle) || ',' || v_runnumber || ');'; execute sql; "EXECUTE of SELECT ... INTO is not implemented" --B_3429963574_9027228 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable
I have a PG function ( using= plpgsql) that calls a number of sub functions also in plpgsql.  I have= two main problems that seem to be variations on the same theme of running d= ynamic SQL from a variable with the EXECUTE statement and returning the resu= lts back to a variable defined in the calling function.

=
Problem 1:  return the results of a table query to a variable.

I have a logging table that my sub functions write to.=  At the beginning of my main function I want to read a run number from= the logging table and increment it by one to then pass into my sub function= s.  I've properly declared the variable (v_runnumber) and the data type= is correct.  The following statement works fine in the main function a= nd stores the value in the variable.

  select = max(runnumber) into v_runnumber from MySchema.log_table;

However, MySchema is a parameter that gets passed into the main functi= on because I need this to work for multiple schemas.  If I try and make= this dynamic by using the following statement:

Sql= :=3D 'select max(run number) into v_runnumber from ' || MySchema || '.log_tab= le;';
Execute Sql;

I get the following er= ror message (even though the resulting value in the text variable Sql is val= id code):

ERROR: query string argument of EXECUTE is null

SQL state: 2= 2004


<= /p>


Problem 2: retu= rning the results of a function call to a variable.


This is a similar issue to #1 but in th= is case, I'm calling a function from the main function and trying to get the= return value back (a single integer) from the sub function to test for erro= rs.  Again, I'm calling the function with  dynamical SQL because o= f the need to take user values from the main function to call the sub functi= ons.  The function call:


sql :=3D 'select * from public.elt_set= _locking(1,' || quote_literal(tenant) || ','  || quote_literal(app) || = ','  || quote_literal(cycle) || ','  || v_runnumber || ');';

execute sql;


Works f= ine.  However when I try and store the value coming back from the funct= ion into a main variable with the following call I get an error:
<= br>
sql :=3D 'select * into v_retcode from public.elt_set_locki= ng(1,' || quote_literal(tenant) || ','  || quote_literal(app) || ',' &n= bsp;|| quote_literal(cycle) || ','  || v_runnumber || ');';
&= nbsp;execute sql;

"EXECUTE of SELECT ... INTO= is not implemented"
--B_3429963574_9027228--