Re: [GENERAL] SQL plan in functions
On Thu, Dec 18, 2008 at 9:18 AM, Julius Tuskenis julius.tuske...@gmail.com wrote: Hello, list. I have a function witch returns record set. What troubles me is that depending on parameters I pass to this function the execution time varies greatly. On the other hand if I execute the query (not the function) with pgAdmin - it gives results quickly. In previous post You helped me realize, that the problem was because the function has only one plan of SQL inside no matter the parameters values. Is there a way to order postgres to check the plan each time the function is called? use EXECUTE 'query' . -- GJ -- 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] SQL plan in functions
In response to Julius Tuskenis : Hello, list. I have a function witch returns record set. What troubles me is that depending on parameters I pass to this function the execution time varies greatly. On the other hand if I execute the query (not the function) with pgAdmin - it gives results quickly. In previous post You helped me realize, that the problem was because the function has only one plan of SQL inside no matter the parameters values. Is there a way to order postgres to check the plan each time the function is called? Sure, use EXECUTE 'your sql' inside the function to force the planner to generate a new plan depending on the current parameters. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] SQL plan in functions
Thank you Andreas and Grzegorz. It worked! While experimenting I found that if I wright FOR rec IN EXECUTE 'my sql' LOOP .. its OK, but If I wright RETURN QUERY EXECUTE 'my sql' I get syntax error. Is it a bug? Julius Tuskenis A. Kretschmer rašė: In response to Julius Tuskenis : Hello, list. I have a function witch returns record set. What troubles me is that depending on parameters I pass to this function the execution time varies greatly. On the other hand if I execute the query (not the function) with pgAdmin - it gives results quickly. In previous post You helped me realize, that the problem was because the function has only one plan of SQL inside no matter the parameters values. Is there a way to order postgres to check the plan each time the function is called? Sure, use EXECUTE 'your sql' inside the function to force the planner to generate a new plan depending on the current parameters. Regards, Andreas -- 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] SQL plan in functions
On 18/12/2008 12:12, Julius Tuskenis wrote: While experimenting I found that if I wright FOR rec IN EXECUTE 'my sql' LOOP .. its OK, but If I wright RETURN QUERY EXECUTE 'my sql' I get syntax error. Is it a bug? No, it's a syntax error. :-) You need to do something like this to return the rows from the query: FOR rec in EXECUTE 'your sql here' LOOP RETURN NEXT rec; END LOOP; RETURN; -- exits from the function. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] SQL plan in functions
Yes, Raymond - I know how to return record set in function using FOR, but since version 8.3 there is a option - using RETURN QUERY SELECT something FROM somewhere;. So if it works for SELECT why should it not work for EXECUTE ? Julius Tuskenis Raymond O'Donnell rašė: On 18/12/2008 12:12, Julius Tuskenis wrote: While experimenting I found that if I wright FOR rec IN EXECUTE 'my sql' LOOP .. its OK, but If I wright RETURN QUERY EXECUTE 'my sql' I get syntax error. Is it a bug? No, it's a syntax error. :-) You need to do something like this to return the rows from the query: FOR rec in EXECUTE 'your sql here' LOOP RETURN NEXT rec; END LOOP; RETURN; -- exits from the function. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] SQL plan in functions
On 18/12/2008 12:40, Julius Tuskenis wrote: Yes, Raymond - I know how to return record set in function using FOR, but since version 8.3 there is a option - using RETURN QUERY SELECT something FROM somewhere;. So if it works for SELECT why should it not work for EXECUTE ? Oh - I didn't know about thatso I'm afraid I don't know why it's causing a problem for you :-) Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
