Skip site navigation (1) Skip section navigation (2)

Re: inserting boolean values in dynamic queries

From: "Raphael Bauduin" <rblists(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: inserting boolean values in dynamic queries
Date: 2008-09-18 13:53:50
Message-ID: f5227160809180653q3f72a379i91e212b2bdce271c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Thu, Sep 18, 2008 at 2:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Raphael Bauduin" <rblists(at)gmail(dot)com> 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

In response to

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2008-09-18 14:06:02
Subject: Re: inserting boolean values in dynamic queries
Previous:From: Tom LaneDate: 2008-09-18 12:35:47
Subject: Re: inserting boolean values in dynamic queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group