Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to concatenate a boolean to a text string for an EXECUTE stmt in a stored proc

How to concatenate a boolean to a text string for an EXECUTE stmt in a stored proc

From
Keaton Adams
Date:

Postgres 8.1 on RHEL

How do I formulate this EXECUTE statement so that cust_hold (boolean column in the table) can be set to the value in v_cust_on_hold (boolean variable in a function)?

v_cust_on_hold      BOOLEAN;
 
EXECUTE 'UPDATE customer_action_ytd_' || v_tyear || ' ' ||
  'SET bytes_sc = bytes_sc + ' || v_bytes_sc || ', ' ||
   'cust_hold= ' || v_cust_on_hold || ', ' ||
   'cust_count = cust_count + ' || v_cust_count || ' ' ||
   'WHERE id = ' || v_id || ' ' ||
   'AND cust_type = \'' || v_cust_type || '\' ';
 
 
psql:runit.sql:1: ERROR:  operator does not exist: text || boolean
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
CONTEXT:  SQL statement "SELECT  'UPDATE customer_action_ytd_' ||  $1  || ' ' || 'SET bytes_sc = bytes_sc + ' ||  $2  || ', ' || 'cust_hold = ' ||  $3  || ', ' || 'cust_count = cust_count + ' ||  $4  || ' ' || 'WHERE id = ' ||  $5  || ' ' || 'AND cust_type = \'' ||  $6  || '\' '"
PL/pgSQL function "kda_test_ytd_rollup" line 96 at execute statement
 
I tried a TO_CHAR(v_cust_on_hold) but received:

psql:runit.sql:1: ERROR:  function to_char(boolean) does not exist

Thanks,

Keaton

 

Re: How to concatenate a boolean to a text string for an EXECUTE stmt in a stored proc

From
Martijn van Oosterhout
Date:
On Wed, Mar 19, 2008 at 02:28:33PM -0600, Keaton Adams wrote:
>
> Postgres 8.1 on RHEL
>
> How do I formulate this EXECUTE statement so that cust_hold (boolean column
> in the table) can be set to the value in v_cust_on_hold (boolean variable in
> a function)?

You're looking for quote_literal(), see the documentation:
http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html
Section 35.8.4.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
Attachment

Re: How to concatenate a boolean to a text string for an EXECUTE stmt in a stored proc

From
"Gurjeet Singh"
Date:
On Thu, Mar 20, 2008 at 1:58 AM, Keaton Adams <kadams@mxlogic.com> wrote:

Postgres 8.1 on RHEL

How do I formulate this EXECUTE statement so that cust_hold (boolean column in the table) can be set to the value in v_cust_on_hold (boolean variable in a function)?

v_cust_on_hold      BOOLEAN;
 
EXECUTE 'UPDATE customer_action_ytd_' || v_tyear || ' ' ||
  'SET bytes_sc = bytes_sc + ' || v_bytes_sc || ', ' ||
   'cust_hold= ' || v_cust_on_hold || ', ' ||
   'cust_count = cust_count + ' || v_cust_count || ' ' ||
   'WHERE id = ' || v_id || ' ' ||
   'AND cust_type = \'' || v_cust_type || '\' ';
 
 
psql:runit.sql:1: ERROR:  operator does not exist: text || boolean
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
CONTEXT:  SQL statement "SELECT  'UPDATE customer_action_ytd_' ||  $1  || ' ' || 'SET bytes_sc = bytes_sc + ' ||  $2  || ', ' || 'cust_hold = ' ||  $3  || ', ' || 'cust_count = cust_count + ' ||  $4  || ' ' || 'WHERE id = ' ||  $5  || ' ' || 'AND cust_type = \'' ||  $6  || '\' '"
PL/pgSQL function "kda_test_ytd_rollup" line 96 at execute statement
 
I tried a TO_CHAR(v_cust_on_hold) but received:

psql:runit.sql:1: ERROR:  function to_char(boolean) does not exist


You can simply use the CAST operator like so:

declare
    t text;
    b boolean;
begin
    b = true;
    t = 'SELECT 1 where true = ' || b::text || ';';
    raise notice '%', t;
end;


    One more thing, I noticed that you are trying to escape single quotes (') with backslashes (\), in the last line of the EXECUTE:

'AND cust_type = \'' || v_cust_type || '\' ';

    You might want to do it like this:

'AND cust_type = ''' || v_cust_type || ''' ';

    Thats the SQL standard way of escaping single quotes; just precede them with another quote.

HTH,

Best regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device