Thread: raise is not working
Hello again everyone. I need some help once again. I am following the postgresql pl/pgsql docs and trying to have my function show me the query it is trying to run since it not returning the expected results. However, it does not appear that the raise option is working. Can anyone please point me to what is wrong, or what server options need to be turned on. 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. Anyway, here is a code snippet of what I am trying to do: 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; --------------- SQL_Str is defined as a varchar. Neither of the raise calls have done anything, but I don't get any errors either. I'm running 7.3.4. Thanks, Chris
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
"CHRIS HOOVER" <CHRIS.HOOVER@companiongroup.com> writes:
> SQL_Str is defined as a varchar. Neither of the raise calls have done
> anything, but I don't get any errors either.
The only way RAISE EXCEPTION "isn't going to do anything" is if control
doesn't get to it. My bet would be that you are invoking some other
function than you think you are --- we've seen examples of that sort of
mistake recently. Check for multiple functions with same name and
different argument types.
regards, tom lane
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
"CHRIS HOOVER" <CHRIS.HOOVER@companiongroup.com> writes:
> Sorry for the delay, here is the function.
> CREATE OR REPLACE FUNCTION "public"."clmhdr_grid_query" (varchar, varchar,
> varchar, varchar, varchar, varchar) RETURNS SETOF "public"."clmhdr" AS'
> ...
> SQL_Str := "select * from clmhdr";
If those are really double quotes then I'd expect you to be getting
errors along the lines of
ERROR: column "select * from clmhdr" does not exist
long before you get to the RAISEs. You need doubled single quotes, egSQL_Str := ''select * from clmhdr'';
See the docs.
regards, tom lane
tom,
They were a double quote. I switched all of the double quotes out for single
quotes but still not go. The function appears to run and return no results
but, I don't get any raises. I have even tried moving the raises to be fire
first lines in the function, but to no avail.
That first assignment was ment to set SQL_Str to hold the value: select * from
clmhdr. I don't think I need to single quotes. That would set it to 'select
* from clmhdr', right???
I'm very confused as to why I'm not seeing anything here.
I looked at my settings, and client_min_messages, and server_min_messages are
both set to the default of notice.
Thanks for any help,
Chris
------------------( Forwarded letter 1 follows )---------------------
Date: Thu, 23 Sep 2004 12:52:07 -0400
To: chris.hoover
Cc: Josh.Berkus[josh]@agliodbs.com.comp, pgsql-sql@postgresql.org.comp
From: Tom.Lane[tgl]@sss.pgh.pa.us.comp
Sender: pgsql-sql-owner+m19055@postgresql.org.comp
Subject: Re: [SQL] raise is not working
"CHRIS HOOVER" <CHRIS.HOOVER@companiongroup.com> writes:
> Sorry for the delay, here is the function.
> CREATE OR REPLACE FUNCTION "public"."clmhdr_grid_query" (varchar, varchar,
> varchar, varchar, varchar, varchar) RETURNS SETOF "public"."clmhdr" AS'
> ...
> SQL_Str := "select * from clmhdr";
If those are really double quotes then I'd expect you to be getting
errors along the lines of
ERROR: column "select * from clmhdr" does not exist
long before you get to the RAISEs. You need doubled single quotes, egSQL_Str := ''select * from clmhdr'';
See the docs.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
"CHRIS HOOVER" <CHRIS.HOOVER@companiongroup.com> writes:
> They were a double quote. I switched all of the double quotes out for single
> quotes but still not go. The function appears to run and return no results
> but, I don't get any raises. I have even tried moving the raises to be fire
> first lines in the function, but to no avail.
Hmm. We saw a case just the other day where someone was mystified why
they weren't getting reasonable results, and it turned out that what
they were doing was editing function foo(something) and then invoking
a pre-existing function foo(somethingelse). Check for similarly-named
functions with different parameter lists ...
regards, tom lane