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

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 variablein
> a function)?

You're looking for quote_literal(), see the documentation:
http://www.postgresql.org/docs/8.3/i...tatements.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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFH5m5pIB7bNG8LQkwRAvqcAJ9ysiFBfWjDDE8eZrJF3/dGdEPiqwCfbQTA
15ZdeXVDS5ZQ8j1BFnaACMw=
=yW4W
-----END PGP SIGNATURE-----

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