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

Re: BUG #5001: can not prepare for where $1 is null

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: 94487509(at)qq(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5001: can not prepare for where $1 is null
Date: 2009-08-21 16:52:06
Message-ID: 162867790908210952j6572e589o1e23fa77aa703316@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Hello

2009/8/21  <94487509(at)qq(dot)com>:
>
> The following bug has been logged online:
>
> Bug reference:      5001
> Logged by:
> Email address:      94487509(at)qq(dot)com
> PostgreSQL version: 8.3.3
> Operating system:   linux
> Description:        can not prepare for where $1 is null
> Details:
>
> why can not prepare like this? thanks!
>  prepare ssss as select * from test where $1 is null;
>  ERROR:  could not determine data type of parameter $1

Probably you are thinking, so first parameter is name of column. But
this is wrong idea. You cannot parametrize column or table names. In
this case - first parameter will be constant expression - with null
value it returns all rows, with not null value - it returns no row.

If you wont to do filter query via some name you have to use dynamic sql:

create or replace function ssss(_name varchar)
returns setof test as $$
declare _r record;
begin
  for _r in execute 'select * from test where ' || quote_ident(_name)
|| ' is null'
  loop
    return next r;
  end loop;
  return;
end;
$$ language plpgsql;

regards
Pavel Stehule



>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

In response to

pgsql-bugs by date

Next:From: Greg StarkDate: 2009-08-21 16:55:44
Subject: Re: 8.4.0 data loss / HOT-related bug
Previous:From: Tom LaneDate: 2009-08-21 16:51:45
Subject: Re: BUG #5004: pg_freespacemap make a SegFault

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