Re: [GENERAL] plpgsql question: select into multiple variables ?
Hi Yari, Thanks for the response. You did make the “simplified concept” function more rational. However, This was kind of a non-sense function to demonstrate the problem I was having with the “select fields” and the “into variables”. As pointed out by Adrian Klaver and Tom Lane, the real problem was in casts that I was using were confusing the parser and were un-necessary. Appreciate your thought and effort. Regards Dave From: Yasin Sari [mailto:yasinsar...@googlemail.com] Sent: Tuesday, June 30, 2015 3:26 AM To: Day, David Subject: Re: [GENERAL] plpgsql question: select into multiple variables ? Hi David, this works for me. CREATE OR REPLACE FUNCTION sys.time_test ( out first_weekend date, out last_weekend date ) RETURNS SETOF record AS $body$ BEGIN SELECT COALESCE(MIN(CAL_DATE),'01-jun-2014'),COALESCE(MAX(CAL_DATE),'01-jun-2014') into first_weekend,last_weekend FROM sys.calendar WHERE month_of_year = (extract(MONTH FROM current_date))::int AND year_of_date = (extract(YEAR FROM current_date))::int AND day_of_week IN ( 'Sat','Sun'); return next; END $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER COST 100 ROWS 1000; On Mon, Jun 29, 2015 at 10:07 PM, Day, David mailto:d...@redcom.com>> wrote: Hi, Postgres version 9.3.9 What is wrong with my usage of the plpgsql "select into" concept I have a function to look into a calendar table to find the first and Last weekend date of a month. In this simplified concept function I end up with a NULL for first or last weekend variable. create or replace function sys.time_test () returns date as $$ DECLARE first_weekend date; last_weekend date; BEGIN SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend::date, last_weekend::date FROM sys.calendar WHERE month_of_year = (extract(MONTH FROM current_date))::int AND year_of_date = (extract(YEAR FROM current_date))::int AND day_of_week IN ( 'Sat','Sun'); RETURN( COALESCE(last_weekend,'01-jun-2014')); END $$ LANGUAGE plpgsql volatile; If I execute the same select logic from a psql shell I get the correct result. (1 row) ace_db=# ace_db=# SELECT MIN(CAL_DATE),MAX(CAL_DATE) FROM sys.calendar cal WHERE cal.month_of_year = (extract(MONTH FROM current_date))::int AND cal.year_of_date = (extract(YEAR FROM current_date))::int AND cal.day_of_week IN ( 'Sat','Sun'); min |max + 2015-06-06 | 2015-06-28 (1 row) If I simplify to a single variable it works. i.e create or replace function sys.time_test () returns date as $$ DECLARE first_weekend date; last_weekend date; BEGIN SELECT MIN(CAL_DATE) INTO first_weekend::date FROM sys.calendar WHERE month_of_year = (extract(MONTH FROM current_date))::int AND year_of_date = (extract(YEAR FROM current_date))::int AND day_of_week IN ( 'Sat','Sun'); RETURN( COALESCE(first_weekend,'01-jun-2014')); END $$ LANGUAGE plpgsql volatile; I suppose I can adjust to write my actual function to have 2 selects; one for each variable. However, I thought according to the documentation the targets could/must match the result columns for select into ? Thoughts Thanks Dave Day -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql question: select into multiple variables ?
"David G. Johnston" writes: > On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane wrote: >> ... So what you wrote here is equivalent to >> >> SELECT MIN(CAL_DATE),MAX(CAL_DATE) ::date, last_weekend::date INTO >> first_weekend FROM sys.calendar ... > âDoes it help to recognize the fact that "first_weekend::âdate" is not a > valid identifier name (because it is lacking double-quotes)? No. You're supposing that we *should* reject this case, which is not true given the current rules. As a counterexample consider SELECT INTO x - y FROM foo which per current rules means SELECT - y INTO x FROM foo The only real difference between this and the :: case is that :: doesn't come in a prefix-operator form, but that's an awfully weak reed to hang a cross-language syntax rule on. >> To make this noticeably better, we'd probably have to insist that >> INTO come at the end of the SELECT list, > Are you missing a "not" here? No, I'm not. See previous example. To detect errors more completely, we'd need a rule that what follows the INTO clause be "FROM" and nothing else (well, maybe "GROUP BY" and some other cases, but in any case a fully reserved word). As things stand, to support INTO-someplace-else we have to suppose that anything other than identifiers and commas is not part of INTO but belongs to the SELECT expression list. It's precisely the lack of any clear delimiter between INTO's arguments and the main SELECT syntax that is biting us, and as long as we allow INTO somewhere other than after the SELECT expression list, we can't have a delimiter because of the historical choice not to. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql question: select into multiple variables ?
On Mon, Jun 29, 2015 at 4:27 PM, Tom Lane wrote: > Adrian Klaver writes: > > On 06/29/2015 12:07 PM, Day, David wrote: > >> What is wrong with my usage of the plpgsql "select into" concept > >> I have a function to look into a calendar table to find the first and > >> Last weekend date of a month. > >> > >> create or replace function sys.time_test () > >> returns date as > >> $$ > >> DECLARE > >> first_weekend date; > >> last_weekend date; > >> BEGIN > >> > >> SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend::date, > last_weekend::date FROM sys.calendar ... > > > The ::date cast seem to be the problem. > > Indeed. Here's what's happening: the argument of INTO can basically only > be a list of variable names. (Well, they can be qualified field names, > but certainly not cast expressions.) And there's this messy legacy syntax > rule that says the INTO clause can be anywhere inside the SELECT list. > So what happens is the plpgsql parser reads "INTO first_weekend", notes > the next token is :: which can't be part of INTO, and drops back to > handling the rest of the input as SELECT text. So what you wrote here is > equivalent to > > SELECT MIN(CAL_DATE),MAX(CAL_DATE) ::date, last_weekend::date INTO > first_weekend FROM sys.calendar ... > > which accidentally looks like perfectly valid SELECT syntax. And I think > it doesn't complain about "too many output columns" either. So you end up > with no reported error and very confusing results. > Does it help to recognize the fact that "first_weekend::date" is not a valid identifier name (because it is lacking double-quotes)? It knows that "::" cannot be part of INTO but it is in the middle of reading the characters of an identifier and without quoting it cannot one of those either. Can that be made to take precedence and at least cause this specific case to fail? > To make this noticeably better, we'd probably have to insist that > INTO come at the end of the SELECT list, Are you missing a "not" here? "...insist that INTO not come at the end of the SELECT list"? It does seem any other location results in a syntax error - including in between the two select-list columns (i.e., MAX(...) INTO var1::date, var2::date MIN(...)) > which would break lots and > lots of existing client code ... so I'm not holding my breath. > > Moral of the story: being user-friendly by accepting sloppy syntax > is not an unalloyed win. > > From the documentation: """ SELECT select_expressions INTO [STRICT] target FROM ...; If a row or a variable list is used as target, the query's result columns must exactly match the structure of the target as to number and data types, or else a run-time error occurs. When a record variable is the target, it automatically configures itself to the row type of the query result columns. The INTO clause can appear almost anywhere in the SQL command. Customarily it is written either just before or just after the list of select_expressions in a SELECT command, or at the end of the command for other command types. It is recommended that you follow this convention in case the PL/pgSQL parser becomes stricter in future versions. """ I've never really liked the above "customarily" advice and do so less given this example. For all other statement types the INTO is the last clause written and while that may not be what experienced people default to doing it seems reasonable, safe, and consistent to suggest the same location for SELECT queries while noting that indeed its position just before or after the select list are common in the wild. The comment about becoming stricter should probably just be removed because, as noted, it ain't gonna happen. I'll admit that this all is not likely worth a great deal of effort given the lack of complains and the obviousness of the problem's manifestation. But it is the case that the lack of an error occurs in the recommended syntax form. David J.
Re: [GENERAL] plpgsql question: select into multiple variables ?
-Original Message- From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] Sent: Monday, June 29, 2015 4:03 PM To: Day, David; pgsql-general@postgresql.org Subject: Re: [GENERAL] plpgsql question: select into multiple variables ? On 06/29/2015 12:07 PM, Day, David wrote: > Hi, > > > > Postgres version 9.3.9 > > > What is wrong with my usage of the plpgsql "select into" concept I > have a function to look into a calendar table to find the first and > Last weekend date of a month. > > In this simplified concept function I end up with a NULL for first or last > weekend variable. > > > create or replace function sys.time_test () returns date as $$ DECLARE > first_weekend date; > last_weekend date; > BEGIN > >SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend::date, > last_weekend::date FROM sys.calendar >WHERE month_of_year = (extract(MONTH FROM current_date))::int AND >year_of_date = (extract(YEAR FROM current_date))::int AND > day_of_week IN ( 'Sat','Sun'); > > RETURN( COALESCE(last_weekend,'01-jun-2014')); > > END > $$ > LANGUAGE plpgsql volatile; The ::date cast seem to be the problem. When I tried a version of the function here with them I got the same output. Eliminating them got the correct output. They are redundant as you already DECLAREd first_weekend and last_weekend to be DATE type. So: SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend, last_weekend . > > > If I execute the same select logic from a psql shell I get the correct result. > > > (1 row) > > ace_db=# ace_db=# SELECT MIN(CAL_DATE),MAX(CAL_DATE) FROM sys.calendar cal > > WHERE cal.month_of_year = > (extract(MONTH FROM current_date))::int AND > > cal.year_of_date = (extract(YEAR FROM current_date))::int AND > > cal.day_of_week IN ( 'Sat','Sun'); > min |max > + > 2015-06-06 | 2015-06-28 > (1 row) > > > If I simplify to a single variable it works. i.e > > > create or replace function sys.time_test () returns date as $$ DECLARE > first_weekend date; > last_weekend date; > BEGIN > >SELECT MIN(CAL_DATE) INTO first_weekend::date FROM sys.calendar >WHERE month_of_year = (extract(MONTH FROM current_date))::int AND >year_of_date = (extract(YEAR FROM current_date))::int AND > day_of_week IN ( 'Sat','Sun'); > > RETURN( COALESCE(first_weekend,'01-jun-2014')); > > END > $$ > LANGUAGE plpgsql volatile; > > > > I suppose I can adjust to write my actual function to have 2 selects; one for > each variable. > However, I thought according to the documentation the targets could/must > match the result columns for select into ? > > > Thoughts > > > Thanks > > > Dave Day > > > > -- Adrian Klaver adrian.kla...@aklaver.com I agree with your evaluation. I originally had that, but in playing around with the function had added the casts with no benefit and seemingly no harm either. I later noted that I was comparing to 'SAT','SUN' rather then 'Sat','Sun' in my calendar table. After repairing that I forgot to back out the date cast. Although the cast was redundant as you pointed out. I am not quite sure why it made it not work. Nontheless, I am happy to move on to other issues. Thanks very much for your assistance. Dave Day -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql question: select into multiple variables ?
Adrian Klaver writes: > On 06/29/2015 12:07 PM, Day, David wrote: >> What is wrong with my usage of the plpgsql "select into" concept >> I have a function to look into a calendar table to find the first and >> Last weekend date of a month. >> >> create or replace function sys.time_test () >> returns date as >> $$ >> DECLARE >> first_weekend date; >> last_weekend date; >> BEGIN >> >> SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend::date, >> last_weekend::date FROM sys.calendar ... > The ::date cast seem to be the problem. Indeed. Here's what's happening: the argument of INTO can basically only be a list of variable names. (Well, they can be qualified field names, but certainly not cast expressions.) And there's this messy legacy syntax rule that says the INTO clause can be anywhere inside the SELECT list. So what happens is the plpgsql parser reads "INTO first_weekend", notes the next token is :: which can't be part of INTO, and drops back to handling the rest of the input as SELECT text. So what you wrote here is equivalent to SELECT MIN(CAL_DATE),MAX(CAL_DATE) ::date, last_weekend::date INTO first_weekend FROM sys.calendar ... which accidentally looks like perfectly valid SELECT syntax. And I think it doesn't complain about "too many output columns" either. So you end up with no reported error and very confusing results. To make this noticeably better, we'd probably have to insist that INTO come at the end of the SELECT list, which would break lots and lots of existing client code ... so I'm not holding my breath. Moral of the story: being user-friendly by accepting sloppy syntax is not an unalloyed win. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql question: select into multiple variables ?
On 06/29/2015 12:07 PM, Day, David wrote: Hi, Postgres version 9.3.9 What is wrong with my usage of the plpgsql "select into" concept I have a function to look into a calendar table to find the first and Last weekend date of a month. In this simplified concept function I end up with a NULL for first or last weekend variable. create or replace function sys.time_test () returns date as $$ DECLARE first_weekend date; last_weekend date; BEGIN SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend::date, last_weekend::date FROM sys.calendar WHERE month_of_year = (extract(MONTH FROM current_date))::int AND year_of_date = (extract(YEAR FROM current_date))::int AND day_of_week IN ( 'Sat','Sun'); RETURN( COALESCE(last_weekend,'01-jun-2014')); END $$ LANGUAGE plpgsql volatile; The ::date cast seem to be the problem. When I tried a version of the function here with them I got the same output. Eliminating them got the correct output. They are redundant as you already DECLAREd first_weekend and last_weekend to be DATE type. So: SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend, last_weekend . If I execute the same select logic from a psql shell I get the correct result. (1 row) ace_db=# ace_db=# SELECT MIN(CAL_DATE),MAX(CAL_DATE) FROM sys.calendar cal WHERE cal.month_of_year = (extract(MONTH FROM current_date))::int AND cal.year_of_date = (extract(YEAR FROM current_date))::int AND cal.day_of_week IN ( 'Sat','Sun'); min |max + 2015-06-06 | 2015-06-28 (1 row) If I simplify to a single variable it works. i.e create or replace function sys.time_test () returns date as $$ DECLARE first_weekend date; last_weekend date; BEGIN SELECT MIN(CAL_DATE) INTO first_weekend::date FROM sys.calendar WHERE month_of_year = (extract(MONTH FROM current_date))::int AND year_of_date = (extract(YEAR FROM current_date))::int AND day_of_week IN ( 'Sat','Sun'); RETURN( COALESCE(first_weekend,'01-jun-2014')); END $$ LANGUAGE plpgsql volatile; I suppose I can adjust to write my actual function to have 2 selects; one for each variable. However, I thought according to the documentation the targets could/must match the result columns for select into ? Thoughts Thanks Dave Day -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql question: select into multiple variables ?
Hi, Postgres version 9.3.9 What is wrong with my usage of the plpgsql "select into" concept I have a function to look into a calendar table to find the first and Last weekend date of a month. In this simplified concept function I end up with a NULL for first or last weekend variable. create or replace function sys.time_test () returns date as $$ DECLARE first_weekend date; last_weekend date; BEGIN SELECT MIN(CAL_DATE),MAX(CAL_DATE) INTO first_weekend::date, last_weekend::date FROM sys.calendar WHERE month_of_year = (extract(MONTH FROM current_date))::int AND year_of_date = (extract(YEAR FROM current_date))::int AND day_of_week IN ( 'Sat','Sun'); RETURN( COALESCE(last_weekend,'01-jun-2014')); END $$ LANGUAGE plpgsql volatile; If I execute the same select logic from a psql shell I get the correct result. (1 row) ace_db=# ace_db=# SELECT MIN(CAL_DATE),MAX(CAL_DATE) FROM sys.calendar cal WHERE cal.month_of_year = (extract(MONTH FROM current_date))::int AND cal.year_of_date = (extract(YEAR FROM current_date))::int AND cal.day_of_week IN ( 'Sat','Sun'); min |max + 2015-06-06 | 2015-06-28 (1 row) If I simplify to a single variable it works. i.e create or replace function sys.time_test () returns date as $$ DECLARE first_weekend date; last_weekend date; BEGIN SELECT MIN(CAL_DATE) INTO first_weekend::date FROM sys.calendar WHERE month_of_year = (extract(MONTH FROM current_date))::int AND year_of_date = (extract(YEAR FROM current_date))::int AND day_of_week IN ( 'Sat','Sun'); RETURN( COALESCE(first_weekend,'01-jun-2014')); END $$ LANGUAGE plpgsql volatile; I suppose I can adjust to write my actual function to have 2 selects; one for each variable. However, I thought according to the documentation the targets could/must match the result columns for select into ? Thoughts Thanks Dave Day -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql question
On Monday, December 05, 2011 6:59:32 am Gauthier, Dave wrote: > v8.3.4 on linux > > Is there a way to set the query used in a "for rec in (query) loop -> end > loop" be a variable? Example > > if (foo = 'whatever') > then > sqlstmt := "select x,y,z ..."; > else > sqlstmt := "select a,b,c ..."; > end if ; > > for therec in > sqlstmt > loop > ... > end loop; http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING If I am following the above right then the: FOR target IN EXECUTE text_expression LOOP form is what you are looking for. I have not used variable substitution for this form, so you will need to test. > > > Thanks in Advance for any help. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql question
v8.3.4 on linux Is there a way to set the query used in a "for rec in (query) loop -> end loop" be a variable? Example if (foo = 'whatever') then sqlstmt := "select x,y,z ..."; else sqlstmt := "select a,b,c ..."; end if ; for therec in sqlstmt loop ... end loop; Thanks in Advance for any help.
Re: [GENERAL] plpgsql question
Hello you used a wrong syntax see http://www.pgsql.cz/index.php/PL/pgSQL_%28en%29#Usage_PL.2FpgSQL_function_with_parametres_of_type_table Regards Pavel Stehule 2010/8/28 Jon Griffin : > I am trying to calculate a value from a current record in a query and can't > seem to get it working. > > Here is the shortened query; > SELECT > s.id, > r.the_date_time, > s.open_price, > s.high_price, > s.low_price, > s.close_price, > thesheet_onepair.symbol, > r.buy_long, > r.buy_stop, > r.sell_cl, > r.stop_sell, > r.sell_short, > r.sell_stop, > r.buy_os, > r.stop_buy, > check_long_profit ()as tst > FROM > public.thesheet_dailystats s, > public.thesheet_recommendation r, > public.thesheet_onepair > WHERE > s.one_pair_id = thesheet_onepair.id AND > s.the_date = r.the_date_time::date AND > r.one_pair_id = thesheet_onepair.id > ORDER BY > r.the_date_time DESC, > thesheet_onepair.id ASC; > > > check_long_profit is the function. I basically want to do some calculations > on this record and return some calculated fields. > > I can't seem to figure out how to pass the row. > > Here is my header for the function > create or replace function check_long_profit () returns integer AS $$ > > I know I am missing something easy. > > Thanks > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql question
I am trying to calculate a value from a current record in a query and can't seem to get it working. Here is the shortened query; SELECT s.id, r.the_date_time, s.open_price, s.high_price, s.low_price, s.close_price, thesheet_onepair.symbol, r.buy_long, r.buy_stop, r.sell_cl, r.stop_sell, r.sell_short, r.sell_stop, r.buy_os, r.stop_buy, check_long_profit ()as tst FROM public.thesheet_dailystats s, public.thesheet_recommendation r, public.thesheet_onepair WHERE s.one_pair_id = thesheet_onepair.id AND s.the_date = r.the_date_time::date AND r.one_pair_id = thesheet_onepair.id ORDER BY r.the_date_time DESC, thesheet_onepair.id ASC; check_long_profit is the function. I basically want to do some calculations on this record and return some calculated fields. I can't seem to figure out how to pass the row. Here is my header for the function create or replace function check_long_profit () returns integer AS $$ I know I am missing something easy. Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql question
Terminology point: you used the word "aggregate" but the function below doesn't have an aggregate. Aggregates are functions thatoperate on multiple rows, like count() and sum(); substr() doesn'tdo that so it's not an aggregate. ya. my mistake.[snip] 1. Create a composite type with the desired columns, declare the function to return SETOF that type, and declare row to be of that type.k. this is where i was confused. this is exactly what i wanted/neededThanks Yahoo! Photos Showcase holiday pictures in hardcover Photo Books. You design it and well bind it!
Re: [GENERAL] plpgsql question
On Mon, Jan 09, 2006 at 01:01:33PM -0800, Matthew Peter wrote: > One other quick question, (figure it still applies to the subject > line :) when returning a row from a function I'm trying to include an > aggregate, but it's not showing up in the query result and I think > it's because it's not included in the RETURN NEXT row;? How do I > return it as part of the resultset... Terminology point: you used the word "aggregate" but the function below doesn't have an aggregate. Aggregates are functions that operate on multiple rows, like count() and sum(); substr() doesn't do that so it's not an aggregate. > create or replace function getrecord(int,text) RETURNS SETOF my_tbl as $$ > DECLARE > row my_tbl%rowtype; > > BEGIN > FOR row IN SELECT *, SUBSTR(title,1,25) as short_title FROM my_tbl [...] You've declared the row variable to be of type my_tbl so whatever columns my_tbl has are the columns you get. If you want to return additional columns then you have a few choices: 1. Create a composite type with the desired columns, declare the function to return SETOF that type, and declare row to be of that type. 2. Declare the function to return SETOF record, declare row to be of type record, and provide a column definition list when you call the function. 3. Use OUT parameters (new in 8.1). -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] plpgsql question
snip WHERE my_tbl_id = $1AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username ENDor WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE)or WHERE my_tbl_id = $1 AND COALESCE($2, username) = usernameWith predicates such as these you wouldn't need to use EXECUTE andyou could write the query only once. That did work. Thanks. One other quick question, (figure it still applies to the subject line :) when returning a row from a function I'm trying to include an aggregate, but it's not showing up in the query result and I think it's because it's not included in the RETURN NEXT row;? How do I return it as part of the resultset...create or replace function getrecord(int,text) RETURNS SETOF my_tbl as $$DECLARE row my_tbl%rowtype;BEGINFOR row IN SELECT *, SUBSTR(title,1,25) as short_title FROM my_tblWHERE ...LOOPRETURN NEXT row;END LOOP;RETURN;END;$$ LANGUAGE plpgsql; Thanks Yahoo! Photos Got holiday prints? See all the ways to get quality prints in your hands ASAP.
Re: [GENERAL] plpgsql question
Michael Fuhr <[EMAIL PROTECTED]> wrote: On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote:> Michael Fuhr wrote:> > On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:> > > Is it possible to skip the loop and just return all records in a> > > single query and shove all those rows into a table variable?> > > > Not in PL/pgSQL -- you need to return each row with RETURN NEXT,> > generally from within a loop. Why do you want to avoid that?> > I was thinking it would be more efficient to pull all the records in> one call rather than 50 calls. For all I know it probably executes 50> calls in the internals when translating the IN (IDs).I wouldn't worry about that unless you can demonstrate that it'scausing a performance problem. Even then you're stuck becausethat's how set-returning functions work.> > * You could use an IF statement to execute the query you need.> > That's what I was trying to do, but I'm not sure i was doing it in> the right context, since it was IN the query, not testing after it.> Figured I'd ask the list if I was trying something impossible or if> I was close to help get me on track. The IF statement needs to be part of the PL/pgSQL logic, not partof the query string. However, you might be able to use CASE orCOALESCE in the query, as in WHERE my_tbl_id = $1AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username ENDor WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE)or WHERE my_tbl_id = $1 AND COALESCE($2, username) = usernameWith predicates such as these you wouldn't need to use EXECUTE and< br>you could write the query only once.-- Michael Fuhr I'll try that out tomorrow. Thanks Micheal Yahoo! DSL Something to write home about. Just $16.99/mo. or less
Re: [GENERAL] plpgsql question
On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote: > Michael Fuhr <[EMAIL PROTECTED]> wrote: > > On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote: > > > Is it possible to skip the loop and just return all records in a > > > single query and shove all those rows into a table variable? > > > > Not in PL/pgSQL -- you need to return each row with RETURN NEXT, > > generally from within a loop. Why do you want to avoid that? > > I was thinking it would be more efficient to pull all the records in > one call rather than 50 calls. For all I know it probably executes 50 > calls in the internals when translating the IN (IDs). I wouldn't worry about that unless you can demonstrate that it's causing a performance problem. Even then you're stuck because that's how set-returning functions work. > > * You could use an IF statement to execute the query you need. > > That's what I was trying to do, but I'm not sure i was doing it in > the right context, since it was IN the query, not testing after it. > Figured I'd ask the list if I was trying something impossible or if > I was close to help get me on track. The IF statement needs to be part of the PL/pgSQL logic, not part of the query string. However, you might be able to use CASE or COALESCE in the query, as in WHERE my_tbl_id = $1 AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username END or WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE) or WHERE my_tbl_id = $1 AND COALESCE($2, username) = username With predicates such as these you wouldn't need to use EXECUTE and you could write the query only once. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] plpgsql question
Michael Fuhr <[EMAIL PROTECTED]> wrote: On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:> Is it possible to skip the loop and just return all records in a> single query and shove all those rows into a table variable?Not in PL/pgSQL -- you need to return each row with RETURN NEXT,generally from within a loop. Why do you want to avoid that?I was thinking it would be more efficient to pull all the records in one call rather than 50 calls. For all I know it probably executes 50 calls in the internals when translating the IN (IDs). * You could use an IF statement to execute the query you need. That's what I was trying to do, but I'm no t sure i was doing it in the right context, since it was IN the query, not testing after it. Figured I'd ask the list if I was trying something impossible or if I was close to help get me on track. * You could put the queries in separate functions. The query is so similiar (occasionally match on extra WHERE arg) it would be nice just to use a conditional to match if that extra argument is given as not null...rather than maintain two simliar functions if possible, while keeping it planned after the first run.Does using an IF predicate in the WHERE in the SQL call require EXECUTE since (I guess) I'm making the SQL statement somewhat dynamic? All I've been able to find is IF handling after the query, not in it.Thanks againMatt Yahoo! DSL Something to write home about. Just $16.99/mo. or less
Re: [GENERAL] plpgsql question
On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote: > Is it possible to skip the loop and just return all records in a > single query and shove all those rows into a table variable? Not in PL/pgSQL -- you need to return each row with RETURN NEXT, generally from within a loop. Why do you want to avoid that? For simple functions you could use SQL instead of PL/pgSQL: http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html#AEN31627 > Also, the WHERE part is also important cause I'm not sure i got > that part right? Would this call for EXECUTE or will it be okay and > be planned the first time by the query planner? If each call to the function issues the same query, just with different values, then you shouldn't need to use EXECUTE. If the query differs depending on the function parameters then you have several possibilities: * You could build the query string and use EXECUTE. Be sure to read about quote_literal() and quote_ident(). * You could use an IF statement to execute the query you need. * You could put the queries in separate functions. You can use the same name for different functions if their call signatures are different, e.g., getrecord(integer) and getrecord(integer, text). * You could rewrite the query, possibly using CASE or COALESCE to handle NULL values. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] plpgsql question
On 1/5/06, Matthew Peter wrote: > I'm trying to do a simple SELECT * in plpgsql that returns a set of records > as a row w/ columns, not a row into a variable, w/ some conditionals. > > The function below is semi-pseudo with what I'm trying to... If anyone > could give me an example that works by returning it as a resultset > maintaining the columns, that would be awesome and I could take it from > there. > > I've read the pl/pgsql section of the docs and the Douglas book but I'm > still confused on this issue... > > Thanks > > create or replace function getrecord(int,text) RETURNS SETOF records as $$ > DECLARE > -- event := rows to return from the table below > > BEGIN > event := SELECT * FROM my_tbl > WHERE 1 = 1 > and my_tbl_id IN (0$1) || > ' IF $2 IS NOT NULL THEN' || and username = $2 || 'END IF;' > ; -- end sql statement > > RETURN event; > > END; > $$ LANGUAGE plpgsql; > Pandurangan R S <[EMAIL PROTECTED]> wrote: Assuming records is the name of a table...create or replace function getrecord(int,text) RETURNS SETOF records as $$DECLARErow records%rowtype;BEGINFOR row IN SELECT * FROM my_tblWHERE ...LOOPRETURN NEXT row;END LOOP;RETURN;END;$$ LANGUAGE plpgsql;Thanks for the reply. Is it possible to skip the loop and just return all records in a single query and shove all those rows into a table variable? Also, the WHERE part is also important cause I'm not sure i got that part right? Would this call for EXECUTE or will it be okay and be planned the first time by the query planner? Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.
Re: [GENERAL] plpgsql question
Assuming records is the name of a table... create or replace function getrecord(int,text) RETURNS SETOF records as $$ DECLARE row records%rowtype; BEGIN FOR row IN SELECT * FROM my_tbl WHERE ... LOOP RETURN NEXT row; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; On 1/5/06, Matthew Peter <[EMAIL PROTECTED]> wrote: > I'm trying to do a simple SELECT * in plpgsql that returns a set of records > as a row w/ columns, not a row into a variable, w/ some conditionals. > > The function below is semi-pseudo with what I'm trying to... If anyone > could give me an example that works by returning it as a resultset > maintaining the columns, that would be awesome and I could take it from > there. > > I've read the pl/pgsql section of the docs and the Douglas book but I'm > still confused on this issue... > > Thanks > > create or replace function getrecord(int,text) RETURNS SETOF records as $$ > DECLARE > -- event := rows to return from the table below > > BEGIN > event := SELECT * FROM my_tbl > WHERE 1 = 1 > and my_tbl_id IN (0$1) || > ' IF $2 IS NOT NULL THEN' || and username = $2 || 'END IF;' > ; -- end sql statement > > RETURN event; > > END; > $$ LANGUAGE plpgsql; > > > > > > Yahoo! DSL Something to write home about. Just $16.99/mo. or less > > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] plpgsql question
I'm trying to do a simple SELECT * in plpgsql that returns a set of records as a row w/ columns, not a row into a variable, w/ some conditionals. The function below is semi-pseudo with what I'm trying to... If anyone could give me an example that works by returning it as a resultset maintaining the columns, that would be awesome and I could take it from there. I've read the pl/pgsql section of the docs and the Douglas book but I'm still confused on this issue... Thankscreate or replace function getrecord(int,text) RETURNS SETOF records as $$ DECLARE -- event := rows to return from the table belowBEGIN event := SELECT * FROM my_tbl WHERE 1 = 1 and my_tbl_id IN (0$1) || ' IF $2 IS NOT NULL THEN' || and username = $2 || 'END IF;' ; -- end sql statementRETURN event;END; $$ LANGUAGE plpgsql; Yahoo! DSL Something to write home about. Just $16.99/mo. or less
Re: [GENERAL] plpgsql question
DECLARE RowsAffected INTEGER; BEGIN -- DO your statement GET DIAGNOSTICS RowsAffected = ROW_COUNT; END On Tuesday 25 November 2003 02:56 pm, Brian Hirt wrote: > I'm looking to find out how many rows were effected during an update in > a trigger. I ran across this message by jan talking about this feature > possibly being added to postgresql 6.5, but I can't find any reference > to such a feature in the current documentation. Did this ever make it > into postgresql? > > http://archives.postgresql.org/pgsql-sql/1999-02/msg00110.php > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "You're very sure of your facts, " he said at last, "I couldn't trust the thinking of a man who takes the Universe - if there is one - for granted. " ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] plpgsql question
I'm looking to find out how many rows were effected during an update in a trigger. I ran across this message by jan talking about this feature possibly being added to postgresql 6.5, but I can't find any reference to such a feature in the current documentation. Did this ever make it into postgresql? http://archives.postgresql.org/pgsql-sql/1999-02/msg00110.php ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plpgsql question...
Steve Wampler <[EMAIL PROTECTED]> writes:
> PostgreSQL 6.5.3
>
> appdb=> create function insert_or_update() returns opaque as '
> appdb'> begin
> appdb'> insert into attributes_table values(new.id,new.name,
> appdb'>new.units,new.value);
> appdb'> return NULL;
> appdb'> end;'
> appdb-> language 'plpgsql';
> CREATE
> appdb=> create trigger t before insert on attributes for each row
> appdb-> execute procedure insert_or_update();
> CREATE
> appdb=> insert into attributes values('site','prefix','none','kp');
> NOTICE: plpgsql: ERROR during compile of insert_or_update near line 2
> ERROR: syntax error at or near "in"
> appdb=>
> =
> Does anyone see what I've done wrong?
Nothing that I can see. I copied and pasted this trigger into current
sources and it worked fine. Ditto for your other example.
There must be something pretty broken about your copy of plpgsql;
dunno what exactly. I'd recommend updating to 7.0.2 and then seeing
if the problem persists. If it does we can dig deeper.
regards, tom lane
