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

From: Keaton Adams <kadams(at)mxlogic(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: How to concatenate a boolean to a text string for an EXECUTE stmt in a stored proc
Date: 2008-03-19 20:28:33
Message-ID: C406D391.3200%kadams@mxlogic.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bjørn T Johansen 2008-03-19 21:17:00 Which JDBC version to use with PostgreSQL 8.1.11?
Previous Message patrick 2008-03-19 20:11:22 Re: tsearch2 in postgresql 8.3.1 - invalid byte sequence for encoding "UTF8": 0xc3