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;
