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
