From: | "CHRIS HOOVER" <CHRIS(dot)HOOVER(at)companiongroup(dot)com> |
---|---|
To: | Josh Berkus <josh(at)agliodbs(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: raise is not working |
Date: | 2004-09-23 16:31:00 |
Message-ID: | NY81152d-0e236341@companiongroup.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Sorry for the delay, here is the function.
Chris
----------
CREATE OR REPLACE FUNCTION "public"."clmhdr_grid_query" (varchar, varchar,
varchar, varchar, varchar, varchar) RETURNS SETOF "public"."clmhdr" AS'
Declare
Last_Name varchar;
First_Name varchar;
Patient_Control_Number varchar;
Claim_Create_Date_From varchar;
Claim_Create_Date_To varchar;
Claim_User_ID varchar;
Clmhdr_Rec clmhdr%ROWTYPE;
SQL_Str varchar;
Where_Clause boolean;
Begin
Last_Name := $1;
First_Name := $2;
Patient_Control_Number := $3;
Claim_Create_Date_From := $4;
Claim_Create_Date_To := $5
Claim_User_ID := $6;
SQL_Str := "select * from clmhdr";
Where_Clause := False;
-- Building the where clause
if ( Last_Name is not null ) then
SQL_Str := SQL_Str || " where hdr_pat_l_name = " ||
quote_literal(Last_Name);
Where_Clause := True;
end if;
if ( First_name is not null ) then
if (Where_Clause) then
SQL_Str := SQL_Str || " and hdr_pat_f_name = " ||
quote_literal(First_Name);
else
SQL_Str := SQL_Str || " where hdr_pat_f_name = " ||
quote_literal(First_Name);
Where_Clause := True;
end if;
end if;
if ( Patient_Control_Number is not null ) then
if (Where_Clause) then
SQL_Str := SQL_Str || " and hdr_pat_cntl_nbr = " ||
quote_literal(Patient_Control_Number);
else
SQL_Str := SQL_Str || " where hdr_pat_cntl_nbr = " ||
quote_literal(Patient_Control_Number);
Where_Clause := True;
end if;
end if;
if ( Claim_Create_Date_From is not null ) then
if (Where_Clause) then
SQL_Str := SQL_Str || " and hdr_create_dt >= " ||
quote_literal(Claim_Create_Date_From);
else
SQL_Str := " where hdr_create_dt >= " ||
quote_literal(Claim_Create_Date_From);
Where_Clause := True;
end if;
end if;
if ( Claim_Create_Date_To is not null ) then
if (Where_Clause) then
SQL_Str := SQL_Str || " and hdr_create_dt <= " ||
quote_literal(Claim_Create_Date_To);
else
SQL_Str := SQL_Str || " where hdr_create_dt <= " ||
quote_literal(Claim_Create_Date_To);
Where_Clause := True;
end if;
end if;
if ( Claim_User_ID is not null ) then
if (Where_Clause) then
SQL_Str := SQL_Str || " and hdr_user_id = " ||
quote_literal(Claim_User_ID);
else
SQL_Str := SQL_Str || " where hdr_user_id = " ||
quote_literal(Claim_User_ID);
Where_Clause := True;
end if;
end if;
SQL_Str := SQL_Str || "limit 15000;";
RAISE NOTICE ''''SQL STRING = %'''', SQL_Str;
raise exception ''''THIS SUCKS!'''';
for Clmhdr_rec in execute SQL_Str loop
return next Clmhdr_rec;
end loop;
return;
end;
'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
------------------( Forwarded letter 1 follows )---------------------
Date: Tue, 21 Sep 2004 10:53:27 -0700
To: pgsql-sql(at)postgresql(dot)org(dot)comp
Cc: chris.hoover
From: Josh(dot)Berkus[josh](at)agliodbs(dot)com(dot)comp
Sender: pgsql-sql-owner+m19040(at)postgresql(dot)org(dot)comp
Subject: Re: [SQL] raise is not working
Chris,
> I have tried setting both server_min_messages (all the way down to debug5),
> and client_min_messages (to debug1), and I still do not get a responce. I
> did bounce the server after these changes.
Please paste your entire function definition, and a copy of your interactive
session on psql (assuming you're using psql; if you're using a GUI tool, that
could be the problem). I've a feeling that your function is erroring out
*before* it gets to the raise.
--
Josh Berkus
Aglio Database Solutions
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-09-23 16:52:07 | Re: raise is not working |
Previous Message | Tom Lane | 2004-09-23 16:19:39 | Re: plpgsql syntax |