Re: raise is not working - Mailing list 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_Tovarchar; 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 :=
$5Claim_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@postgresql.org.comp
Cc: chris.hoover
From: Josh.Berkus[josh]@agliodbs.com.comp
Sender: pgsql-sql-owner+m19040@postgresql.org.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?
              http://www.postgresql.org/docs/faqs/FAQ.html