pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedraise is not working
7+ messages / 3 participants
[nested] [flat]
* raise is not working
@ 2004-09-21 16:44 CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 2 replies; 7+ messages in thread
From: CHRIS HOOVER @ 2004-09-21 16:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: raise is not working
@ 2004-09-21 17:53 Josh Berkus <josh@agliodbs.com>
parent: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
1 sibling, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2004-09-21 17:53 UTC (permalink / raw)
To: pgsql-sql; +Cc: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: raise is not working
@ 2004-09-21 22:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2004-09-21 22:29 UTC (permalink / raw)
To: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: raise is not working
@ 2004-09-23 16:31 CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 1 reply; 7+ messages in thread
From: CHRIS HOOVER @ 2004-09-23 16:31 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: 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@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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: raise is not working
@ 2004-09-23 16:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2004-09-23 16:52 UTC (permalink / raw)
To: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-sql
"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, eg
SQL_Str := ''select * from clmhdr'';
See the docs.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: raise is not working
@ 2004-09-23 17:04 CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 1 reply; 7+ messages in thread
From: CHRIS HOOVER @ 2004-09-23 17:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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, eg
SQL_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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: raise is not working
@ 2004-09-23 17:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2004-09-23 17:11 UTC (permalink / raw)
To: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-09-23 17:11 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-21 16:44 raise is not working CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
2004-09-21 17:53 ` Josh Berkus <josh@agliodbs.com>
2004-09-21 22:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-23 16:31 Re: raise is not working CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
2004-09-23 16:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-23 17:04 Re: raise is not working CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
2004-09-23 17:11 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox