On Thu, Sep 18, 2008 at 2:35 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Raphael Bauduin" <[EMAIL PROTECTED]> writes:
>> EXECUTE 'UPDATE tbl SET '
>> || quote_ident(colname)
>> || ' = '
>> || quote_literal(newvalue)
>> || ' WHERE key = '
>> || quote_literal(keyvalue);
>
>> It works fine, except when I want to include a boolean value: the cast
>> of newvalue from boolean to text causes problem.
>
> What problem? 'true' and 'false' are accepted as input for boolean
> AFAICS.
>
yes, but I have problems to use them to build the query passed to execute.
For example, working on this table
create table test(b_val bool);
I want to create a function that I cal call as
select test_bool(true)
and that will insert an entry in this test table.
Below are several attemps, all unsuccessful. The way I've made it work
it by accepting a char as input, t or f:
create or replace function test_bool(val char(1)) returns void as $$
create or replace function test_bool(val bool) returns void as $$
BEGIN
RAISE INFO 'insert into test(b_val) values (''%'')', val;
execute 'insert into test (b_val) values ('|| val || ')';
END
$$
language plpgsql;
--> ERROR: array value must start with "{" or dimension information
create or replace function test_bool(val bool) returns void as $$
BEGIN
RAISE INFO 'insert into test(b_val) values (''%'')', val;
execute 'insert into test (b_val) values ('|| val::text || ')';
END
$$
language plpgsql;
--> ERROR: cannot cast type boolean to text
create or replace function test_bool(val bool) returns void as $$
BEGIN
RAISE INFO 'insert into test(b_val) values (''%'')', val;
execute 'insert into test (b_val) values ('|| quote_literal(val) || ')';
END
$$
language plpgsql;
--> ERROR: function quote_literal(boolean) does not exist
I guess I'm missing something....
Thanks.
Raphaël
> regards, tom lane
>
--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
