[SQL] returning values to variables from dynamic SQL

[James Sharrett]

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"

[SQL] returning values from dynamic SQL to a variable

[James Sharrett]

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"

[SQL] ERROR: missing FROM-clause entry for table "new"

[James Sharrett]

I'm trying to define a trigger function that looks for changes in table A
(table the trigger for the function is on) and write a delta record into
table B.  So if a record has a value of 100 in table A, and it is updated to
50, the function should write ­50 in table B. I can get the trigger to work
with static SQL statements but for the actual code, I need to use dynamic
SQL because I need to alter the insert statement to B depending on what
column in table A is altered.  I can get the correct SQL generated but when
I execute the string inside the trigger function I get an error because it
doesn't seem to be able to see the NEW table when it's run with EXECUTE.

So, this works in the trigger function:

Insert into A (col1,col2,ŠcolN)
Select new.col1,new.co2Šnew.colN)

This doesn't:

sql := 'Insert into A (col1,col2,ŠcolN) ';
sql := sql || 'Select new.col1,new.co2Šnew.colN)';
Execute sql;

ERROR:  missing FROM-clause entry for table "new"



There is nothing wrong with the resulting code from sql because if I output
the string and put it in as static SQL in my trigger it works.



How do I build the string within the trigger and execute it with a reference
to NEW?


Thanks in advance for the help,
James

Re: [SQL] ERROR: missing FROM-clause entry for table "new"

[James Sharrett]

> I'm trying to define a trigger function that looks for changes in table A
> (table the trigger for the function is on) and write a delta record into table
> B.  So if a record has a value of 100 in table A, and it is updated to 50, the
> function should write ­50 in table B. I can get the trigger to work with
> static SQL statements but for the actual code, I need to use dynamic SQL
> because I need to alter the insert statement to B depending on what column in
> table A is altered.  I can get the correct SQL generated but when I execute
> the string inside the trigger function I get an error because it doesn't seem
> to be able to see the NEW table when it's run with EXECUTE.
> 
> So, this works in the trigger function:
> 
> Insert into A (col1,col2,ŠcolN)
> Select new.col1,new.co2Šnew.colN)
> 
> This doesn't:
> 
> sql := 'Insert into A (col1,col2,ŠcolN) ';
> sql := sql || 'Select new.col1,new.co2Šnew.colN)';
> Execute sql;
> 
> ERROR:  missing FROM-clause entry for table "new"
> 
> 
> 
> There is nothing wrong with the resulting code from sql because if I output
> the string and put it in as static SQL in my trigger it works.
> 
> 
> 
> How do I build the string within the trigger and execute it with a reference
> to NEW?
> 
> 
> Thanks in advance for the help,
> James
> 

Please read all of:

http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGS
QL-STATEMENTS-EXECUTING-DYN

But especially 39.5.4

You want to make use of format and/or USING to pass in the values to a
parameterized dynamic statement.

Note I linked to 9.2 but any recent version should have the behavior, if
different section numbers.

In short the whole "NEW.name" is a variable and you need to build the
statement the same way you would with any user-defined variable.

David J.


-

Thanks for the reference David.  I'm now able to get the sql statement to
run as dynamic sql with the following syntax

> sql := 'Insert into A (col1,col2,ŠcolN) ';
> sql := sql || 'values($1,$2,Š$N )';
> Execute sql USING new.col1,new.col2Šnew.colN
But that still leaves me with the problem that new.col1 ­ colN aren't known
till runtime.  My list of columns could vary from 5 to 50 depending on the
specific update scenario.  Inside the sql string I can dynamically build $1
- $N using a counter in my loop that gets the appropriate column list but
how do I dynamically build the USING list?  I tried put in a text variable
that contained a delimited list of columns as such:

list = new.col1,new.col2Šnew.colN
> sql := 'Insert into A (col1,col2,ŠcolN) ';
> sql := sql || 'values($1,$2,Š$N )';
> Execute sql USING list
> 
> But that gives the error:
>   ERROR:  there is no parameter $2
LINE 1: ...endcategory_id,time_id,metric,amount)  values ($1,$2,$3,$4,$...

[SQL] returning the number of rows output by a copy command from a function

[James Sharrett]

I have a function that generates a table of records and then a SQL statement
that does a COPY into a text file.  I want to return the number of records
output into the text file from my function.  The number of rows in the table
is not necessarily the number of rows in the file due to summarization of
data in the table on the way out.  Here is a very shortened version of what
I'm doing:


CREATE OR REPLACE FUNCTION export_data(list of parameters)
  RETURNS integer AS
$BODY$

declare
My variables

Begin

 { A lot of SQL to build and populate the table of records to export}


strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with CSV
HEADER;';
Execute strSQL;

Return 0;

end
$BODY$
  LANGUAGE plpgsql VOLATILE

strSQL gets dynamically generated so it's not a static statement.

This all works exactly as I want.  But when I try to get the row count back
out I cannot get it.  I've tried the following:

1.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with CSV
HEADER;';
Execute strSQL into export_count;

Return export_count;

This give me an error saying that I've tried to use the INTO statement with
a command that doesn't return data.


2.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with CSV
HEADER;';
Execute strSQL;

Get diagnostics export_count = row_count;

This always returns zero.

3.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with CSV
HEADER;';
Execute strSQL;

Return row_count;

This returns a null.

Any way to do this?


Thanks in advance,
James

Re: [SQL] returning the number of rows output by a copy command from a function

[James Sharrett]

The # rows in the table <> # rows in the file because the table is grouped
and aggregated so simple table row count wouldn't be accurate.  The table
can run in the 75M - 100M range so I was trying to avoid running all the
aggregations once to output the file and then run the same code again just
to get a count. 




On 1/16/13 11:36 AM, "Rob Sargent"  wrote:

>On 01/16/2013 09:30 AM, James Sharrett wrote:
>> I have a function that generates a table of records and then a SQL
>> statement that does a COPY into a text file.  I want to return the
>> number of records output into the text file from my function.  The
>> number of rows in the table is not necessarily the number of rows in the
>> file due to summarization of data in the table on the way out.  Here is
>> a very shortened version of what I'm doing:
>>
>>
>> CREATE OR REPLACE FUNCTION export_data(list of parameters)
>>RETURNS integer AS
>> $BODY$
>>
>> declare
>> My variables
>>
>> Begin
>>
>>   { A lot of SQL to build and populate the table of records to export}
>>
>>
>> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
>> CSV HEADER;';
>> Execute strSQL;
>>
>> Return 0;
>>
>> end
>> $BODY$
>>LANGUAGE plpgsql VOLATILE
>>
>> strSQL gets dynamically generated so it's not a static statement.
>>
>> This all works exactly as I want.  But when I try to get the row count
>> back out I cannot get it.  I've tried the following:
>>
>> 1.
>> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
>> CSV HEADER;';
>> Execute strSQL into export_count;
>>
>> Return export_count;
>>
>> This give me an error saying that I've tried to use the INTO statement
>> with a command that doesn't return data.
>>
>>
>> 2.
>> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
>> CSV HEADER;';
>> Execute strSQL;
>>
>> Get diagnostics export_count = row_count;
>>
>> This always returns zero.
>>
>> 3.
>> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
>> CSV HEADER;';
>> Execute strSQL;
>>
>> Return row_count;
>>
>> This returns a null.
>>
>> Any way to do this?
>>
>>
>> Thanks in advance,
>> James
>>
>declare export_count int;
>
>select count(*) from export_table into export_count();
>raise notice 'Exported % rows', export_count;
>
>
>
>-- 
>Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-sql




-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] returning the number of rows output by a copy command from a function

[James Sharrett]

The problem I have is that I get nothing back when the COPY is run inside
the function other than what I explicitly return from the function so I
don't have anything to parse.  It's odd that the record count in the
function is treated differently than from sql query in GET DIAGNOSTIC
since the format and information in the string (when run outside of the
function) are exactly the same.




On 1/16/13 11:42 AM, "Adrian Klaver"  wrote:

>On 01/16/2013 08:30 AM, James Sharrett wrote:
>> I have a function that generates a table of records and then a SQL
>> statement that does a COPY into a text file.  I want to return the
>> number of records output into the text file from my function.  The
>> number of rows in the table is not necessarily the number of rows in the
>> file due to summarization of data in the table on the way out.  Here is
>> a very shortened version of what I'm doing:
>>
>>
>> CREATE OR REPLACE FUNCTION export_data(list of parameters)
>>RETURNS integer AS
>> $BODY$
>>
>> declare
>> My variables
>>
>> Begin
>>
>>   { A lot of SQL to build and populate the table of records to export}
>>
>>
>> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
>> CSV HEADER;';
>> Execute strSQL;
>>
>> Return 0;
>>
>> end
>> $BODY$
>>LANGUAGE plpgsql VOLATILE
>>
>> strSQL gets dynamically generated so it's not a static statement.
>>
>> This all works exactly as I want.  But when I try to get the row count
>> back out I cannot get it.  I've tried the following:
>>
>> 1.
>> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
>> CSV HEADER;';
>> Execute strSQL into export_count;
>>
>> Return export_count;
>>
>> This give me an error saying that I've tried to use the INTO statement
>> with a command that doesn't return data.
>>
>>
>> 2.
>> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
>> CSV HEADER;';
>> Execute strSQL;
>>
>> Get diagnostics export_count = row_count;
>>
>> This always returns zero.
>>
>> 3.
>> strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
>> CSV HEADER;';
>> Execute strSQL;
>>
>> Return row_count;
>>
>> This returns a null.
>>
>> Any way to do this?
>
>If it helps:
>http://www.postgresql.org/docs/9.2/interactive/sql-copy.html
>"
>On successful completion, a COPY command returns a command tag of the form
>
>COPY count
>The count is the number of rows copied.
>"
>
>So it looks like you will need to parse the string for the count.
>
>
>>
>>
>> Thanks in advance,
>> James
>>
>
>
>-- 
>Adrian Klaver
>adrian.kla...@gmail.com




-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] returning the number of rows output by a copy command from a function

[James Sharrett]

Integer



On 1/16/13 12:21 PM, "Adrian Klaver"  wrote:

>On 01/16/2013 09:16 AM, James Sharrett wrote:
>> The problem I have is that I get nothing back when the COPY is run
>>inside
>> the function other than what I explicitly return from the function so I
>> don't have anything to parse.  It's odd that the record count in the
>> function is treated differently than from sql query in GET DIAGNOSTIC
>> since the format and information in the string (when run outside of the
>> function) are exactly the same.
>>
>
>Execute strSQL into export_count;
>
>What is export_count DECLAREd as?
>
>
>-- 
>Adrian Klaver
>adrian.kla...@gmail.com




-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] how can I replace all instances of a pattern

[James Sharrett]

I'm trying remove all instances of non-alphanumeric or underscore characters
from a query result for further use.  This is part of a function I'm writing
that is in plpgsql

Examples:

  Original value
'My text1'
'My text 2'
'My-text-3'
'My_text4'
'My!text5'

   Desired
'Mytext1'
'Mytext2'
'Mytext3'
'My_text4'  (no change)
'Mytext5'


The field containing the text is column_name.  I tried the following:

  Select regexp_replace(column_name,'\W','') from mytable

This deals with the correct characters but only does the first instance of
the character so the output is:

'My text1'
'Mytext 2'  (wrong)
'Mytext-3'  (wrong)
'My_text4'
'My!text5'

I managed to get the desired output by writing the text into a variable
through a loop and then just keep looping on the variable until all the
characters are removed:

sql_qry:= 'select column_name from mytable';

for sql_record in execute sql_qry loop
curr_record := sql_record.column_name;

while length(substring(curr_record from '\W'))>0 loop
   curr_record := regexp_replace(curr_record, '\W','');
end loop;

Š. rest of the code

This works but it seems like a lot of work to do something this simple but I
cannot find any function that will replace all instances of a string AND can
base it on a regular expression pattern.  Is there a better way to do this
in 9.1?

Re: [SQL] how can I replace all instances of a pattern

[James Sharrett]

Sorry, caught a typo.  Mytext1 is correctly replaced because only one
instance of the character (space) is in the string.

This deals with the correct characters but only does the first instance of
the character so the output is:

'Mytext1'
'Mytext 2'  (wrong)
'Mytext-3'  (wrong)
'My_text4'
'My!text5'

Re: [SQL] how can I replace all instances of a pattern

[James Sharrett]

Thanks Ken!  I missed that option going through the documentation.

>




-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] the value of OLD on an initial row insert

[James Sharrett]

I have a number of trigger functions on a table that are performing
various calculations.  The table is a column-wise orientation with
multiple columns that could be updated on a single row.  In one of the
triggers, I'm performing a calculation but don't want the code to run if
the OLD and NEW values are the same value.  This can be resulting from
other triggers that are running on the table.  If there is a truly NEW
(non-NULL) value, I want to run the code.

To deal with this, I'm using the following test in my code where I loop
through the columns that could be updated and test to determine which
column on the row is getting a value assigned.


EXECUTE 'SELECT (' ||quote_literal(NEW) || '::' || TG_RELID::regclass
||').' || quote_ident(metric_record.column_name) INTO changed_metric;
   
if not changed_metric is null then

EXECUTE 'SELECT (' ||quote_literal(OLD) || '::' || TG_RELID::regclass
||').' || quote_ident(metric_record.column_name) INTO old_value;

if changed_metric <> old_value then

{calculation code}



This is all doing exactly what I want when the row exists.  However, I
think I'm getting an error if there is a new row getting generated.  I'm
getting the following error when the code runs sometimes:

ERROR: record "old" is not assigned yet
SQL state: 55000
Detail: The tuple structure of a not-yet-assigned record is indeterminate.

Is this what's happening?  If so, how can I avoid the issue.



Thanks,
James




-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql