Quick Links

Trying to select records via matching timestamptz with varchar parameters

From:	"ben wilko" <wilko_ben(at)hotmail(dot)com>
To:	pgsql-novice(at)postgresql(dot)org
Subject:	Trying to select records via matching timestamptz with varchar parameters
Date:	2006-08-09 00:12:50
Message-ID:	BAY116-F192A4BF9CD801215B438998F550@phx.gbl
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-novice

<html><div style='background-color:'>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;     return pointer;  end;  $BODY$   LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION return_daily_card_list(d "varchar", m "varchar", y "varchar") OWNER TO postgres;
<DIV class=RTE>ERROR:  syntax error at or near "$1" at character 44 QUERY:   select * from daily_time_record where if  $1  = SELECT EXTRACT(YEAR FROM daily_time_record.daily_time_card_date) and  $2  = SELECT EXTRACT(MONTH FROM daily_time_record.daily_time_card_date) and  $3  = SELECT EXTRACT(DAY FROM daily_time_record.daily_time_card_date) and if CONTEXT:  SQL statement in PL/PgSQL function "return_daily_card_list" near line 15 </DIV>
<DIV class=RTE>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,   CONSTRAINT daily_time_record_pkey PRIMARY KEY (daily_time_card_id) ) WITHOUT OIDS; ALTER TABLE daily_time_record OWNER TO postgres;</DIV></div></html>

Attachment	Content-Type	Size
unknown_filename	text/html	2.2 KB

Responses

Re: Trying to select records via matching timestamptz with at 2006-08-09 00:35:53 from Stephan Szabo

Browse pgsql-novice by date

	From	Date	Subject
Next Message	Stephan Szabo	2006-08-09 00:35:53	Re: Trying to select records via matching timestamptz with
Previous Message	Steve Crawford	2006-08-08 19:44:35	Re: Scheduling data input from tab delimited file via php