Thread: Trying to select records via matching timestamptz with varchar parameters

Hi Guys
Trying to select records via matching timestamptz with varchar parameters in postgres 8.1, using the 8.1-404 JDBC3
driver.We're also running Java 1.5.0-05. 
CREATE OR REPLACE FUNCTION return_daily_card_list(d "varchar", m "varchar", y "varchar")  RETURNS refcursor
AS$BODY$ declare      pointer refcursor;  begin      open pointer
for         select *          
fromdaily_time_record          where if d = SELECT EXTRACT(YEAR FROM
daily_time_record.daily_time_card_date) and m= SELECT EXTRACT(MONTH FROM
daily_time_record.daily_time_card_date) and y= SELECT EXTRACT(DAY FROM
daily_time_record.daily_time_card_date) andif;    return pointer;  end;  $BODY$ 
LANGUAGE'plpgsql' VOLATILE;ALTER FUNCTION return_daily_card_list(d "varchar", m "varchar", y "varchar") OWNER TO
postgres;
ERROR:  syntax error at or near "$1" at character 44QUERY:   select * from daily_time_record where
if $1  = SELECT EXTRACT(YEAR FROM daily_time_record.daily_time_card_date) and  $2  = SELECT
EXTRACT(MONTHFROM daily_time_record.daily_time_card_date) and  $3  = SELECT EXTRACT(DAY FROM
daily_time_record.daily_time_card_date)and ifCONTEXT:  SQL statement in PL/PgSQL function "return_daily_card_list"
nearline 15 
CREATE TABLE daily_time_record(  daily_time_card_id int4 NOT NULL DEFAULT nextval
'daily_time_record_daily_time_card_id_seq'::regclass), daily_time_card_date timestamptz,  employee_id
varchar(10), agreed_start_time timestamptz,  actual_start_time timestamptz,  total_hours float8, 
CONSTRAINTdaily_time_record_pkey PRIMARY KEY (daily_time_card_id)) WITHOUT OIDS;ALTER TABLE daily_time_record OWNER TO
postgres;

On Wed, 9 Aug 2006, ben wilko wrote:

>
> Hi Guys
>
> Trying to select records via matching timestamptz with varchar parameters in postgres 8.1, using the 8.1-404 JDBC3
driver.
> We're also running Java 1.5.0-05.
>
> CREATE OR REPLACE FUNCTION return_daily_card_list(d "varchar", m "varchar", y "varchar")
>   RETURNS refcursor AS
> $BODY$
> declare
>     pointer refcursor;
> begin
>     open pointer for
>         select *
>         from daily_time_record
>         where
>  if
>  d = SELECT EXTRACT(YEAR FROM daily_time_record.daily_time_card_date)
>  and
>  m = SELECT EXTRACT(MONTH FROM daily_time_record.daily_time_card_date)
>  and
>  y = SELECT EXTRACT(DAY FROM daily_time_record.daily_time_card_date)
>  and if;

I think you want something like:

     open pointer for
         select *
         from daily_time_record
         where
  d = EXTRACT(YEAR FROM daily_time_record.daily_time_card_date)
  and
  m = EXTRACT(MONTH FROM daily_time_record.daily_time_card_date)
  and
  y = EXTRACT(DAY FROM daily_time_record.daily_time_card_date);