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

On Thu, Mar 20, 2008 at 1:58 AM, Keaton Adams <kadams(at)mxlogic(dot)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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device