Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Y8vAH-0002dJ-MA for pgsql-sql@arkaria.postgresql.org; Wed, 07 Jan 2015 18:17:05 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Y8vAH-0007Xk-6r for pgsql-sql@arkaria.postgresql.org; Wed, 07 Jan 2015 18:17:05 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Y8vAF-0007Xd-M4 for pgsql-sql@postgresql.org; Wed, 07 Jan 2015 18:17:04 +0000 Received: from mail-pd0-x22a.google.com ([2607:f8b0:400e:c02::22a]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Y8vAB-0003GS-Fl for pgsql-sql@postgresql.org; Wed, 07 Jan 2015 18:17:01 +0000 Received: by mail-pd0-f170.google.com with SMTP id v10so6115391pde.1 for ; Wed, 07 Jan 2015 10:16:58 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type; bh=FqQCp5dK0BWmuUdp15tuho4ApCHuRY1WX5uiDo16C58=; b=Teh2JlPhtIYuFkBMJCp71DRtLiJTBSP3LP8Y02zREAbPq0wNgtvGkXhA1eHt2TuMbo WBlgQK7MWlfD4fQyzDP+3qxpoMnFTeT7rISrFoQCPexKXjNXiAPore4MuJcIfsrXgUUm /LB+S/5NsJAmRM6TLOCCp6oCxlJU3xXt4Tm8JLCYH6EGWBMmGZXGYBk03wR8vzI++yif 3qaCVuzWM5U0hIuqOlaLOcET+OsdrURlDEolMPTVANF4S5l0MCGA3DTvNC2Sg91XiLdr cGxZ/V1+ix9eyqnQWjwibcbeXk0ktFq63lUdq1B/8ZxcevJrkYnbUFzmMsMbHrWG8APL dGSw== X-Received: by 10.66.102.106 with SMTP id fn10mr7754478pab.156.1420654618035; Wed, 07 Jan 2015 10:16:58 -0800 (PST) Received: from purity.med.utah.edu (purity.med.utah.edu. [155.100.158.98]) by mx.google.com with ESMTPSA id c9sm2426423pdj.52.2015.01.07.10.16.56 for (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 07 Jan 2015 10:16:57 -0800 (PST) Message-ID: <54AD7816.1070209@gmail.com> Date: Wed, 07 Jan 2015 11:16:54 -0700 From: Rob Sargent User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Thunderbird/31.3.0 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Use a TEXT string which is an output from a function for executing a new query in postgres References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------000809040003030102030800" X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------000809040003030102030800 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit On 01/07/2015 11:12 AM, Roy Blum wrote: > I have created a function *myresult()* that receives as input Table > name, and a Prefix, it then creates an SQL one liner command to SELECT > from the specified table only the columns that share the designated > prefix. It output a string which is basically the desired SQL command. > My function is as follows and I show how I call it as well: > > |CREATE OR REPLACEFUNCTION myresult(mytable text, myprefix text) > RETURNS textAS > $func$ > DECLARE > myoneliner text; > BEGIN > SELECT INTO myoneliner > 'SELECT' > || string_agg(quote_ident(column_name::text), ',' ORDER BY column_name) > || ' FROM' || quote_ident(mytable) > FROM information_schema.columns > WHERE table_name= mytable > AND column_nameLIKE myprefix||'%' > AND table_schema= 'public'; -- schema name; might be another param > > RAISE NOTICE'My additional text: %', myoneliner; > RETURN myoneliner; > END > $func$ LANGUAGE plpgsql; > > --now call function > select myresult('dkj_p_k27ac','enri');| > --now call the function: > > |select myresult('dkj_p_k27ac','enri'); | > And now, upon running the above procedure - I get a text string, which > is basically a query (I'll refer to it up next as 'oneliner-output', > just for simplicity). The 'oneline-output' looks as follows (i just > copy/paste it from the one output cell that i've got into here): > > |"SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac"| > > * please note that the double quotes from both sides of the > statement were part of the myresult() output (i didn't add them by > myself). > > > basically, I am able to copy/paste the 'oneliner-output' into a new > postgres query window and execute it as a normal query just fine - > receiving the desired columns and rows in my Data Output window. I > would like however to automate this step, so to avoid the copy/paste > step. Is there a way in postgres to use the TEXT output (the > 'oneliner-output') that I receive from myresult() function, and > execute it? Can a second function be created that would receive the > output of myresult() and use it for executing a query? > > Along these lines, while I know that this scripting works and actually > output exactly the desired columns and rows: > > |--DEALLOCATE stmt1; -- use this line after the first time 'stmt1' was created > prepare stmt1as SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27acFROM dkj_p_k27ac; > execute stmt1;| > I was thinking maybe something like the following scripting could > work, after doing the right tweaking?? Not sure how though.. > > |prepare stmt1as THE_OUTPUT_OF_myresult(); > execute stmt1;| > > Thanks a lot! > Roy Have you looked into "dynamic sql", in which you concoct a statement and the PERFORM it directly from you "myresult" function? --------------000809040003030102030800 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 01/07/2015 11:12 AM, Roy Blum wrote:
I have created a function myresult() that receives as input Table name, and a Prefix, it then creates an SQL one liner command to SELECT from the specified table only the columns that share the designated prefix. It output a string which is basically the desired SQL command. My function is as follows and I show how I call it as well:  

CREATE OR REPLACE FUNCTION myresult(mytable text, myprefix text)
RETURNS text AS 
$func$
DECLARE
   myoneliner text;
BEGIN
   SELECT INTO myoneliner  
          'SELECT '
        || string_agg(quote_ident(column_name::text), ',' ORDER BY column_name)
        || ' FROM ' || quote_ident(mytable)
   FROM   information_schema.columns
   WHERE  table_name = mytable
   AND    column_name LIKE myprefix||'%'
   AND    table_schema = 'public';  -- schema name; might be another param

   RAISE NOTICE 'My additional text: %', myoneliner;
   RETURN myoneliner;
END
$func$ LANGUAGE plpgsql;

--now call function
select myresult('dkj_p_k27ac','enri');
--now call the function:

select myresult('dkj_p_k27ac','enri');
And now, upon running the above procedure - I get a text string, which is basically a query (I'll refer to it up next as 'oneliner-output', just for simplicity). The 'oneline-output' looks as follows (i just copy/paste it from the one output cell that i've got into here):

 
"SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac"

  • please note that the double quotes from both sides of the statement were part of the myresult() output (i didn't add them by myself).

 basically, I am able to copy/paste the 'oneliner-output' into a new postgres query window and execute it as a normal query just fine - receiving the desired columns and rows in my Data Output window. I would like however to automate this step, so to avoid the copy/paste step. Is there a way in postgres to use the TEXT output (the 'oneliner-output') that I receive from myresult() function, and execute it? Can a second function be created that would receive the output of myresult() and use it for executing a query?

Along these lines, while I know that this scripting works and actually output exactly the desired columns and rows:

--DEALLOCATE stmt1; -- use this line after the first time 'stmt1' was created
prepare stmt1 as SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac;
execute stmt1;
I was thinking maybe something like the following scripting could work, after doing the right tweaking?? Not sure how though..

prepare stmt1 as THE_OUTPUT_OF_myresult();
execute stmt1;

Thanks a lot! 
Roy
Have you looked into "dynamic sql", in which you concoct a statement and the PERFORM it directly from you "myresult" function?
--------------000809040003030102030800--