[SQL] plpgsql syntax
I just want to bring up a point for discussion: for the function definition is treated as a long string right now. Why not add an additional token so that we don't have to write 10 single quotes. for example create function a_func() return integer as . end a_func after as and before end a_func marks the definition body. Would this be better than the ' '? Kemin ** Proprietary or confidential information belonging to Ferring Holding SA or to one of its affiliated companies may be contained in the message. If you are not the addressee indicated in this message (or responsible for the delivery of the message to such person), please do not copy or deliver this message to anyone. In such case, please destroy this message and notify the sender by reply e-mail. Please advise the sender immediately if you or your employer do not consent to e-mail for messages of this kind. Opinions, conclusions and other information in this message represent the opinion of the sender and do not necessarily represent or reflect the views and opinions of Ferring. ** ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql syntax
for example create function a_func() return integer as . end a_func after as and before end a_func marks the definition body. Would this be better than the ' '? I guess having a parser that gave better error messages would be nice, too. Yasir ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] plpgsql syntax
Kemin Zhou <[EMAIL PROTECTED]> writes: > I just want to bring up a point for discussion: > for the function definition is treated as a long string right now. Why > not add an additional token so that we don't have to write 10 single quotes. Something's already been done about this for 8.0. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] raise is not working
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: [EMAIL PROTECTED] Cc: chris.hoover From: [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] raise is not working
"CHRIS HOOVER" <[EMAIL PROTECTED]> 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
Re: [SQL] raise is not working
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: [EMAIL PROTECTED], [EMAIL PROTECTED] From: [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Subject: Re: [SQL] raise is not working "CHRIS HOOVER" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] raise is not working
"CHRIS HOOVER" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] need "row number"
Hello all, yes, I know, "row number" isn't a concept that fits into the relational model and I will only be asking for something similar. explanation (actual views below) I have a view that holds the vaccinations scheduled for a patient (v_vaccs_scheduled4pat) depending on what vaccination regimes that patient is on. There are typically between 1 to 5 vaccinations per disease (indication/regime) which is expressed in the vaccination sequence number. Some regimes also have booster shots scheduled. Those boosters are to be given regularly after a set interval. Those have the sequence number field set to NULL. There is a second view that lists all the vaccinations actually given to a patient per regime (v_pat_vacc4ind). This view has dates when the shot was given but no sequence number. I now want to create a view which correlates the two showing me which actual vaccination corresponds to which scheduled vaccination. This is what I cannot get my head wrapped around although it is probably fairly straightforward. The conceptual solution would be to order actual vaccinations by date per regime and number them (remember the "row number" in the subject line ?). One would then join on that with the sequence numbers from the scheduled vaccinations view and treat any actual vaccinations where "row number" > max(sequence number) as being boosters (medically this is correct, btw). Yes, there can and will be several boosters for some regimes. raw data full schema here: http://hherb.com/gnumed/schema/ full schema defs in CVS here: http://savannah.gnu.org/cgi-bin/viewcvs/gnumed/gnumed/gnumed/server/sql/gmClinicalViews.sql relevant excerpt: --- == --- vaccination stuff --- - \unset ON_ERROR_STOP drop view v_vacc_regimes; \set ON_ERROR_STOP 1 create view v_vacc_regimes as select vreg.id as pk_regime, vind.description as indication, _(vind.description) as l10n_indication, vreg.name as regime, coalesce(vreg.comment, '') as comment, vreg.fk_indication as pk_indication, vreg.fk_recommended_by as pk_recommended_by from vacc_regime vreg, vacc_indication vind where vreg.fk_indication = vind.id ; comment on view v_vacc_regimes is 'all vaccination schedules known to the system'; --- - \unset ON_ERROR_STOP drop view v_vacc_defs4reg; \set ON_ERROR_STOP 1 create view v_vacc_defs4reg as select vreg.id as pk_regime, vind.description as indication, _(vind.description) as l10n_indication, vreg.name as regime, coalesce(vreg.comment, '') as reg_comment, vdef.is_booster as is_booster, vdef.seq_no as vacc_seq_no, vdef.min_age_due as age_due_min, vdef.max_age_due as age_due_max, vdef.min_interval as min_interval, coalesce(vdef.comment, '') as vacc_comment, vind.id as pk_indication, vreg.fk_recommended_by as pk_recommended_by from vacc_regime vreg, vacc_indication vind, vacc_def vdef where vreg.id = vdef.fk_regime and vreg.fk_indication = vind.id order by indication, vacc_seq_no ; comment on view v_vacc_defs4reg is 'vaccination event definitions for all schedules known to the system'; --- - \unset ON_ERROR_STOP drop view v_vacc_regs4pat; \set ON_ERROR_STOP 1 create view v_vacc_regs4pat as select lp2vr.fk_patient as pk_patient, vvr.indication as indication, vvr.l10n_indication as l10n_indication, vvr.regime as regime, vvr.comment as comment, vvr.pk_regime as pk_regime, vvr.pk_indication as pk_indication, vvr.pk_recommended_by as pk_recommended_by from lnk_pat2vacc_reg lp2vr, v_vacc_regimes vvr where vvr.pk_regime = lp2vr.fk_regime ; comment on view v_vacc_regs4pat is 'selection of configured vaccination schedules a patient is actually on'; --- - \unset ON_ERROR_STOP drop view v_vaccs_scheduled4pat; \set ON_ERROR_STOP 1 create view v_vaccs_scheduled4pat as select vvr4p.pk_patient as pk_patient, vvr4p.indication as indication, vvr4p.l10n_indication as l10n_indication, vvr4p.regime as regime, vvr4p.comment as reg_comment, vvd4r.is_booster, vvd4r.vacc_seq_no, vvd4r.age_due_min, vvd4r.age_due_max, vvd4r.min_interval, vvd4r.vacc_comment as vacc_comment, vvr4p.pk_regime as pk_regime, vvr4p.pk_indication as pk_indication, vvr4p.pk_recommended_by as pk_recommended_by from v_vacc_regs4pat vvr4p, v_vacc_defs4reg vvd4r where vvd4r.pk_
