Re: [PATCHES] variadic function support
As far as the variadic int versus variadic int[] business, I'm starting to agree with Pavel that variadic int[] offers less potential for confusion. In particular, it seems to make it more obvious for the function author that the argument he receives is an array. Also, the other one would mean that what we put into pg_proc.proargtypes doesn't agree directly with what the user thinks the argument types are. While I think we could force that to work, it's not exactly satisfying the principle of least surprise. One issue that just occurred to me: what if a variadic function wants to turn around and call another variadic function, passing the same array argument on to the second one? This is closely akin to the problem faced by C ... functions, and the solutions are pretty ugly (sprintf vs vsprintf for instance). Can we do any better? At least in the polymorphic case, I'm not sure we can :-(. regards, tom lane maybe with some flag like PARAMS? SELECT least(PARAMS ARRAY[1,2,3,4,5,6]) Regards Pavel Stehule -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] variadic function support
2008/7/14 Florian G. Pflug [EMAIL PROTECTED]: Pavel Stehule wrote: One issue that just occurred to me: what if a variadic function wants to turn around and call another variadic function, passing the same array argument on to the second one? This is closely akin to the problem faced by C ... functions, and the solutions are pretty ugly (sprintf vs vsprintf for instance). Can we do any better? At least in the polymorphic case, I'm not sure we can :-(. maybe with some flag like PARAMS? SELECT least(PARAMS ARRAY[1,2,3,4,5,6]) Just FYI, this is more or less how ruby handles variadic functions - a * before the last argument in the function's *definition* causes all additional arguments to be stored in an array, while a * before the last argument in a function *call* expands an array into single arguments. So, you could e.g do def variadic1(a, b, *c) # c is in array containing all parameters after second one. end def variadic_wrapper(a, *b) variadic1(foobar, a, *b) end So there is precedent for the flag idea too. Plus, I kind of like the idea of using the same syntax for both wrapping and unwrapping of variadic arguments. regards, Florian Pflug ok - it's possible, I''l look in this direction - and it's should be usable in plpgsql - we should be able call variadic functions from plpgsql with immutable number of arguments without dynamic SQL. sample: select mleast(variadic array[1,2,3,4,5]); so I wouldn't do ruby from plpgsql :). Still my goal is well support for libraries like JSON or XML. select json_object(name as 'name', prop as 'prop') -- '[name: , prop: ... It's not strong like SQL/XML, but it is independent on parser, and could exists outside. So my next step is named parameters in SELECT statement. Regards Pavel Stehule -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] variadic function support
2008/7/14 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: sample: select mleast(variadic array[1,2,3,4,5]); Note this would also address Jeff's complaint about not being able to pass zero variadic parameters: select mleast(variadic array[]::int[]); Looks a bit ugly but the type is specified, so it would work correctly with polymorphic functions. Are you intending to change this right now and resubmit, or is it work for later? I prefer separate it to other patch. regards Pavel Stehule regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] variadic function support
2008/7/13 Jeff Davis [EMAIL PROTECTED]: On Tue, 2008-06-24 at 17:10 +0200, Pavel Stehule wrote: Hello this version implements syntax based on argmodes. CREATE FUNCTION mleast(variadic numeric[]) RETURNS numeric AS $$ SELECT min($1[i]) FROM generate_subscripts($1,1) g(i); $$ LANGUAGE SQL; I don't have a strong opinion about whether the variadic argument is declared as an array or scalar, so I am posting my comments about this version of the patch as well. ok This version also has a problem when declaring two functions foo(int) and foo(variadic int[]). In this version, the declaration is allowed but the backend crashes when the function is called. ok, I understand now The variable transform_variadic should have some kind of comment. It seems to be there to distinguish between when you're looking for a candidate function for a function call, and when you're looking for a candidate function for, e.g., CREATE FUNCTION. It's a little hard to follow, and is probably the cause for the aformentioned crash. Also, it doesn't seem to allow calls to a variadic function with zero arguments, e.g. mleast(). I think this should be allowed. It's not possible for all cases, because empty array have be typed array still. But for non polymorphic variadic functions it's probably possible - I would to solve this question later - and for now use overloading etc create function mleast() returns .. create function mleast(variadic params anyarray) returns ... I suggest the following error message rewording: variadic argument isn't an array should be something like: variadic argument must be an array. I invite all you language suggestions. It's really important for me. Regards, Jeff Davis my thanks Pavel Stehule -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] variadic function support
Hello 2008/7/13 Jeff Davis [EMAIL PROTECTED]: On Thu, 2008-06-26 at 17:03 +0200, Pavel Stehule wrote: this is third variant with variadic argumen as scalar. But I still strongly prefer second variant with conformance declared variadic array with used array variable. you checked second or third variant? There are two variants still. Regards Pavel Stehule Please, Tom, can you choose one? This version allows you to declare two functions foo(variadic numeric) and foo(numeric), and then if you do a \df foo the backend crashes. Also, you didn't update an error string: variadic argument isn't an array should (in this version) be something like: can't find array type for variadic parameter type %s I suggest a slightly different wording for the following error messages: aggregate function has variadic argument - variadic parameters not supported for aggregate functions and variadic argument isn't last function's argument - variadic parameter must be the last parameter to the function Those are just suggested wordings. Regards, Jeff Davis -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] review: table function support
Hello,
I am sending actualized patch
Regards
Pavel Stehule
2008/7/9 Pavel Stehule [EMAIL PROTECTED]:
2008/7/9 Marko Kreen [EMAIL PROTECTED]:
Generally, the patch looks fine. There are few issues still:
- plpgsql: the result columns _do_ create local variables.
AIUI, they should not?
it was my mistake - it doesn't do local variables - fixed
- pg_dump: is the psql_assert() introduction necessary, considering it
is used only in one place?
removed - argmode variables is checked before
- There should be regression test for plpgsql too, that test if
the behaviour is correct.
addeded
- The documentation should mention behaviour difference from OUT
parameters.
I will do it.
Wishlist (probably out of scope for this patch):
this is in my wishlist too, but postgresql doesn't support types like
result of functions.
- plpgsql: a way to create record variable for result row. Something like:
CREATE FUNCTION foo(..) RETURNS TABLE (..) AS $$
DECLARE
retval foo%ROWTYPE;
Currently the OUT parameters are quite painful to use due to bad
name resolving logic. Such feature would be perfect replacement.
--
marko
I'll send patch early, thank you much
Regards
Pavel Stehule
*** ./doc/src/sgml/ref/create_function.sgml.orig 2007-09-11 02:06:41.0 +0200
--- ./doc/src/sgml/ref/create_function.sgml 2008-07-09 16:34:26.0 +0200
***
*** 21,27
synopsis
CREATE [ OR REPLACE ] FUNCTION
replaceable class=parametername/replaceable ( [ [ replaceable class=parameterargmode/replaceable ] [ replaceable class=parameterargname/replaceable ] replaceable class=parameterargtype/replaceable [, ...] ] )
! [ RETURNS replaceable class=parameterrettype/replaceable ]
{ LANGUAGE replaceable class=parameterlangname/replaceable
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
--- 21,28
synopsis
CREATE [ OR REPLACE ] FUNCTION
replaceable class=parametername/replaceable ( [ [ replaceable class=parameterargmode/replaceable ] [ replaceable class=parameterargname/replaceable ] replaceable class=parameterargtype/replaceable [, ...] ] )
! [ RETURNS replaceable class=parameterrettype/replaceable
! | RETURNS TABLE ( replaceable class=parametercolname/replaceable replaceable class=parametercoltype/replaceable [, ...] ) ]
{ LANGUAGE replaceable class=parameterlangname/replaceable
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
***
*** 410,415
--- 411,450
/listitem
/varlistentry
+ varlistentry
+ termreplaceable class=parametercolname/replaceable/term
+
+ listitem
+ para
+The name of an output table column.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
+ termreplaceable class=parametercoltype/replaceable/term
+
+ listitem
+ para
+The data type(s) of output table column.
+ /para
+ para
+Depending on the implementation language it might also be allowed
+to specify quotepseudotypes/ such as typecstring/.
+Pseudotypes indicate that the actual argument type is either
+incompletely specified, or outside the set of ordinary SQL data types.
+ /para
+ para
+The type of a column is referenced by writing
+literalreplaceable
+class=parametertablename/replaceable.replaceable
+class=parametercolumnname/replaceable%TYPE/literal.
+Using this feature can sometimes help make a function independent of
+changes to the definition of a table.
+ /para
+ /listitem
+ /varlistentry
+
/variablelist
/refsect1
***
*** 616,622
A commandCREATE FUNCTION/command command is defined in SQL:1999 and later.
The productnamePostgreSQL/productname version is similar but
not fully compatible. The attributes are not portable, neither are the
!different available languages.
/para
para
--- 651,657
A commandCREATE FUNCTION/command command is defined in SQL:1999 and later.
The productnamePostgreSQL/productname version is similar but
not fully compatible. The attributes are not portable, neither are the
!different available languages. TABLE functions are defined in SQL:2003.
/para
para
*** ./doc/src/sgml/xfunc.sgml.orig 2007-11-10 21:14:36.0 +0100
--- ./doc/src/sgml/xfunc.sgml 2008-07-10 10:57:46.0 +0200
***
*** 102,107
--- 102,115
/para
para
+ indextermprimaryTABLE/seealsofunction//An SQL function can
+ declared to return table specified by function's retun table as
+ literalTABLE(replaceablesomecolumns/)/literal. In this case
+ all rows of the last query's result are returned. Furteher details
+ appear bellow.
+ /para
+
+para
The body of an SQL
Re: [PATCHES] review: table function support
2008/7/10 Marko Kreen [EMAIL PROTECTED]:
On 7/10/08, Pavel Stehule [EMAIL PROTECTED] wrote:
I am sending actualized patch
Regards
Pavel Stehule
2008/7/9 Pavel Stehule [EMAIL PROTECTED]:
2008/7/9 Marko Kreen [EMAIL PROTECTED]:
Generally, the patch looks fine. There are few issues still:
- plpgsql: the result columns _do_ create local variables.
AIUI, they should not?
it was my mistake - it doesn't do local variables - fixed
- pg_dump: is the psql_assert() introduction necessary, considering it
is used only in one place?
removed - argmode variables is checked before
- There should be regression test for plpgsql too, that test if
the behaviour is correct.
addeded
- The documentation should mention behaviour difference from OUT
parameters.
I will do it.
Wishlist (probably out of scope for this patch):
this is in my wishlist too, but postgresql doesn't support types like
result of functions.
- plpgsql: a way to create record variable for result row. Something
like:
CREATE FUNCTION foo(..) RETURNS TABLE (..) AS $$
DECLARE
retval foo%ROWTYPE;
Currently the OUT parameters are quite painful to use due to bad
name resolving logic. Such feature would be perfect replacement.
--
marko
I'll send patch early, thank you much
Ok, last items:
- Attached is a patch that fixes couple C comments.
- I think plpgsql 38.1.2 chapter of Supported Argument and Result Data
Types should also have a mention of TABLE functions.
Then I'm content with the patch.
applyed
Regards and thank you very much
Pavel
--
marko
*** ./doc/src/sgml/ref/create_function.sgml.orig 2007-09-11 02:06:41.0 +0200
--- ./doc/src/sgml/ref/create_function.sgml 2008-07-09 16:34:26.0 +0200
***
*** 21,27
synopsis
CREATE [ OR REPLACE ] FUNCTION
replaceable class=parametername/replaceable ( [ [ replaceable class=parameterargmode/replaceable ] [ replaceable class=parameterargname/replaceable ] replaceable class=parameterargtype/replaceable [, ...] ] )
! [ RETURNS replaceable class=parameterrettype/replaceable ]
{ LANGUAGE replaceable class=parameterlangname/replaceable
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
--- 21,28
synopsis
CREATE [ OR REPLACE ] FUNCTION
replaceable class=parametername/replaceable ( [ [ replaceable class=parameterargmode/replaceable ] [ replaceable class=parameterargname/replaceable ] replaceable class=parameterargtype/replaceable [, ...] ] )
! [ RETURNS replaceable class=parameterrettype/replaceable
! | RETURNS TABLE ( replaceable class=parametercolname/replaceable replaceable class=parametercoltype/replaceable [, ...] ) ]
{ LANGUAGE replaceable class=parameterlangname/replaceable
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
***
*** 410,415
--- 411,450
/listitem
/varlistentry
+ varlistentry
+ termreplaceable class=parametercolname/replaceable/term
+
+ listitem
+ para
+The name of an output table column.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
+ termreplaceable class=parametercoltype/replaceable/term
+
+ listitem
+ para
+The data type(s) of output table column.
+ /para
+ para
+Depending on the implementation language it might also be allowed
+to specify quotepseudotypes/ such as typecstring/.
+Pseudotypes indicate that the actual argument type is either
+incompletely specified, or outside the set of ordinary SQL data types.
+ /para
+ para
+The type of a column is referenced by writing
+literalreplaceable
+class=parametertablename/replaceable.replaceable
+class=parametercolumnname/replaceable%TYPE/literal.
+Using this feature can sometimes help make a function independent of
+changes to the definition of a table.
+ /para
+ /listitem
+ /varlistentry
+
/variablelist
/refsect1
***
*** 616,622
A commandCREATE FUNCTION/command command is defined in SQL:1999 and later.
The productnamePostgreSQL/productname version is similar but
not fully compatible. The attributes are not portable, neither are the
!different available languages.
/para
para
--- 651,657
A commandCREATE FUNCTION/command command is defined in SQL:1999 and later.
The productnamePostgreSQL/productname version is similar but
not fully compatible. The attributes are not portable, neither are the
!different available languages. TABLE functions are defined in SQL:2003.
/para
para
*** ./doc/src/sgml/xfunc.sgml.orig 2007-11-10 21:14:36.0 +0100
--- ./doc/src/sgml/xfunc.sgml 2008-07-10
Re: [PATCHES] variadic function support
2008/6/25 Tom Lane [EMAIL PROTECTED]:
Pavel Stehule [EMAIL PROTECTED] writes:
Tom Lane wrote:
Your point about the syntax is good though. It would be better if
the syntax were like
create function foo (a text, variadic b int[])
or maybe even better
create function foo (a text, variadic b int)
I don't see problem with your syntax. It well block combination OUT
and VARIADIC parameter - my one request, variadic parameter have to be
array.
Well, we should certainly store the parameter type as an array in
proargtypes, because that makes this feature transparent to all the
PLs. However, it doesn't follow that the CREATE FUNCTION syntax
has to specify the array type rather than the element type. I think
the Java precedent might be good reason to go with using the element
type in the function declaration.
regards, tom lane
Hello
this is third variant with variadic argumen as scalar. But I still
strongly prefer second variant with conformance declared variadic
array with used array variable.
Regards
Pavel Stehule
*** ./doc/src/sgml/ref/create_function.sgml.orig 2008-06-24 16:46:47.0 +0200
--- ./doc/src/sgml/ref/create_function.sgml 2008-06-24 16:47:46.0 +0200
***
*** 102,108
listitem
para
The mode of an argument: either literalIN/, literalOUT/,
!or literalINOUT/. If omitted, the default is literalIN/.
/para
/listitem
/varlistentry
--- 102,109
listitem
para
The mode of an argument: either literalIN/, literalOUT/,
!literalINOUT/ or literalVARIADIC/literal. If omitted,
!the default is literalIN/.
/para
/listitem
/varlistentry
*** ./doc/src/sgml/xfunc.sgml.orig 2008-06-24 16:53:58.0 +0200
--- ./doc/src/sgml/xfunc.sgml 2008-06-26 13:34:20.0 +0200
***
*** 578,584
para
Parameters can be marked as literalIN/ (the default),
! literalOUT/, or literalINOUT/. An literalINOUT/
parameter serves as both an input parameter (part of the calling
argument list) and an output parameter (part of the result record type).
/para
--- 578,585
para
Parameters can be marked as literalIN/ (the default),
! literalOUT/, literalINOUT/, or literalVARIADIC/literal.
! An literalINOUT/
parameter serves as both an input parameter (part of the calling
argument list) and an output parameter (part of the result record type).
/para
***
*** 805,810
--- 806,833
/screen
/para
/sect2
+
+sect2
+ titleVariadic acronymSQL/acronym Functions/title
+
+ para
+ acronymSQL/acronym functions can be declared to accept
+ variable number of arguments.
+ screen
+ CREATE FUNCTION mleast(variadic args numeric) RETURNS numeric AS $$
+ SELECT min($1[i])
+FROM generate_subscripts($1,1) g(i);
+ $$ LANGUAGE SQL;
+
+ SELECT mleast(10, -1, 5, 4);
+ mleast
+
+ -1
+ (1 row)
+ /screen
+ /para
+/sect2
+
/sect1
sect1 id=xfunc-overload
*** ./src/backend/catalog/namespace.c.orig 2008-06-24 11:24:34.0 +0200
--- ./src/backend/catalog/namespace.c 2008-06-26 16:41:06.0 +0200
***
*** 606,614
int pronargs = procform-pronargs;
int pathpos = 0;
FuncCandidateList newResult;
/* Ignore if it doesn't match requested argument count */
! if (nargs = 0 pronargs != nargs)
continue;
if (OidIsValid(namespaceId))
--- 606,645
int pronargs = procform-pronargs;
int pathpos = 0;
FuncCandidateList newResult;
+ Oid va_oid = InvalidOid;
+ bool variadic = false;
+ bool isnull;
+ Datum proargmodes;
+
+ /*
+ * Search type of variadic argument,
+ */
+ proargmodes = SysCacheGetAttr(PROCOID, proctup,
+ Anum_pg_proc_proargmodes, isnull);
+ if (!isnull)
+ {
+ ArrayType *ar = DatumGetArrayTypeP(proargmodes);
+ char *argmodes;
+ int j;
+
+ argmodes = ARR_DATA_PTR(ar);
+ for (j = 0; j ARR_DIMS(ar)[0]; j++)
+ if (argmodes[j] == PROARGMODE_VARIADIC)
+ {
+ variadic = true;
+ va_oid = get_variadic_element_type(
+ procform-proargtypes.values[j]);
+ Assert(OidIsValid(va_oid));
+ break;
+ }
+ }
/* Ignore if it doesn't match requested argument count */
! if (nargs = 0 pronargs != nargs !variadic)
! continue;
!
! /* Ignore variadic function with less arguments */
! if (nargs = 0 pronargs nargs variadic)
continue;
if (OidIsValid(namespaceId))
***
*** 691,706
/*
* Okay to add it to result list
*/
! newResult = (FuncCandidateList)
! palloc(sizeof(struct _FuncCandidateList) - sizeof(Oid)
! + pronargs * sizeof(Oid));
newResult-pathpos = pathpos;
newResult-oid = HeapTupleGetOid(proctup);
- newResult-nargs = pronargs;
- memcpy
Re: [PATCHES] variadic function support
2008/6/25 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: Tom Lane wrote: Your point about the syntax is good though. It would be better if the syntax were like create function foo (a text, variadic b int[]) or maybe even better create function foo (a text, variadic b int) I don't see problem with your syntax. It well block combination OUT and VARIADIC parameter - my one request, variadic parameter have to be array. Well, we should certainly store the parameter type as an array in proargtypes, because that makes this feature transparent to all the PLs. However, it doesn't follow that the CREATE FUNCTION syntax has to specify the array type rather than the element type. I think the Java precedent might be good reason to go with using the element type in the function declaration. There is only one break - psql functions description. It needs publishing get_element_type function and ofcourse all managers need update. regards Pavel regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] variadic function support
2008/6/25 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: Tom Lane wrote: Your point about the syntax is good though. It would be better if the syntax were like create function foo (a text, variadic b int[]) or maybe even better create function foo (a text, variadic b int) I don't see problem with your syntax. It well block combination OUT and VARIADIC parameter - my one request, variadic parameter have to be array. Well, we should certainly store the parameter type as an array in proargtypes, because that makes this feature transparent to all the PLs. However, it doesn't follow that the CREATE FUNCTION syntax has to specify the array type rather than the element type. I think the Java precedent might be good reason to go with using the element type in the function declaration. it's strange.I looked for some info http://en.wikipedia.org/wiki/Variadic_function#Variadic_functions_in_C.23_and_Java C# use array Does somebody know some about variadic functions in ADA? Regards Pavel Stehule regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] variadic function support
2008/6/25 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: Tom Lane wrote: Your point about the syntax is good though. It would be better if the syntax were like create function foo (a text, variadic b int[]) or maybe even better create function foo (a text, variadic b int) I don't see problem with your syntax. It well block combination OUT and VARIADIC parameter - my one request, variadic parameter have to be array. Well, we should certainly store the parameter type as an array in proargtypes, because that makes this feature transparent to all the PLs. However, it doesn't follow that the CREATE FUNCTION syntax has to specify the array type rather than the element type. I think the Java precedent might be good reason to go with using the element type in the function declaration. I afraid so Java syntax isn't good inspiration http://www.java-tips.org/java-se-tips/java.lang/using-the-varargs-language-feature.html http://www.clanproductions.com/java5.html they use symbol ... like specific synonym to []. public Method getMethod(String name, Class... parameterTypes) I didn't find any info about vararg in Oracle - it uses collection and it allows implicit constructors for emulation o variadic functions - but variadic argument isn't scalar too. So I invite any opinions about it. Regards Pavel Stehule regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] variadic function support
2008/6/25 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: Tom Lane wrote: Your point about the syntax is good though. It would be better if the syntax were like create function foo (a text, variadic b int[]) or maybe even better create function foo (a text, variadic b int) I don't see problem with your syntax. It well block combination OUT and VARIADIC parameter - my one request, variadic parameter have to be array. Well, we should certainly store the parameter type as an array in proargtypes, because that makes this feature transparent to all the PLs. However, it doesn't follow that the CREATE FUNCTION syntax has to specify the array type rather than the element type. I think the Java precedent might be good reason to go with using the element type in the function declaration. I am playing with this now and two versions of proargtypes is 30% more ugly code - mostly pg_dump and paradoxically remove function - because currently RemoveFuncStatement lost argmode, so I am missing info about variadic parameter and I can't simply transformation from element to array. I thing, it isn't good way. Regards Pavel Stehule regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] variadic function support
2008/6/24 Tom Lane [EMAIL PROTECTED]:
Andrew Dunstan [EMAIL PROTECTED] writes:
But if I have
foo( a text, b int[])
it looks odd if both these calls are legal:
foo('a',1,2,3,)
foo('a',ARRAY[1,2,3])
which I understand would be the case with the current patch.
Maybe I misunderstand what is supposed to happen, but I believe that
if the function is marked VARIADIC then the second case would in fact
be rejected: the signature of the function for parameter-matching
purposes is text followed by one or more ints, never text and int[].
I'm also still curious to know how the following would be handled:
foo(a text[], b text[])
I think a is just text[], full stop. Only the last parameter is
interpreted differently for variadic.
Your point about the syntax is good though. It would be better if
the syntax were like
create function foo (a text, variadic b int[])
or maybe even better
create function foo (a text, variadic b int)
since (a) this makes it much more obvious to the reader what the
function might match, and (b) it leaves the door open for marking
multiple parameters as variadic, if we can figure out what that means.
(b) has one disadvantage - argument type is different than real
parameter - and internally it is little bit cleaner (doesn't need
changes in executor). So there is two forces in opposite. a) clean
function's declaration, b) clean function definition. This syntax is
limited - I am not able implement all cases of Oracle's decode
functions - but I hope it's good compromise between functionality and
simplicity.
note - variant b doesn't block multiple parameters as variadic - is
same case as a. array or not array is unimportant - I need different
types so I can choose what is first variadic argument and what is
second.
Academic question is using structured arrays - some like
create or replace function decode(s_value anyelement1, variadic
(s_value anyalement1, o_value anyelement)[])
returns anyelement as $$
select ($2[i]).o_value
from generate_subcripts($1,1) g(i)
where ($2[i]).s_value = $1;
$$ language sql;
regards
Pavel Stehule
regards, tom lane
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] variadic function support
Hello
this version implements syntax based on argmodes.
CREATE FUNCTION mleast(variadic numeric[]) RETURNS numeric AS $$
SELECT min($1[i])
FROM generate_subscripts($1,1) g(i);
$$ LANGUAGE SQL;
Regards
Pavel Stehule
2008/6/24 Tom Lane [EMAIL PROTECTED]:
Andrew Dunstan [EMAIL PROTECTED] writes:
But if I have
foo( a text, b int[])
it looks odd if both these calls are legal:
foo('a',1,2,3,)
foo('a',ARRAY[1,2,3])
which I understand would be the case with the current patch.
Maybe I misunderstand what is supposed to happen, but I believe that
if the function is marked VARIADIC then the second case would in fact
be rejected: the signature of the function for parameter-matching
purposes is text followed by one or more ints, never text and int[].
I'm also still curious to know how the following would be handled:
foo(a text[], b text[])
I think a is just text[], full stop. Only the last parameter is
interpreted differently for variadic.
Your point about the syntax is good though. It would be better if
the syntax were like
create function foo (a text, variadic b int[])
or maybe even better
create function foo (a text, variadic b int)
since (a) this makes it much more obvious to the reader what the
function might match, and (b) it leaves the door open for marking
multiple parameters as variadic, if we can figure out what that means.
regards, tom lane
*** ./doc/src/sgml/ref/create_function.sgml.orig 2008-06-24 16:46:47.0 +0200
--- ./doc/src/sgml/ref/create_function.sgml 2008-06-24 16:47:46.0 +0200
***
*** 102,108
listitem
para
The mode of an argument: either literalIN/, literalOUT/,
!or literalINOUT/. If omitted, the default is literalIN/.
/para
/listitem
/varlistentry
--- 102,109
listitem
para
The mode of an argument: either literalIN/, literalOUT/,
!literalINOUT/ or literalVARIADIC/literal. If omitted,
!the default is literalIN/.
/para
/listitem
/varlistentry
*** ./doc/src/sgml/xfunc.sgml.orig 2008-06-24 16:53:58.0 +0200
--- ./doc/src/sgml/xfunc.sgml 2008-06-24 16:59:42.0 +0200
***
*** 578,584
para
Parameters can be marked as literalIN/ (the default),
! literalOUT/, or literalINOUT/. An literalINOUT/
parameter serves as both an input parameter (part of the calling
argument list) and an output parameter (part of the result record type).
/para
--- 578,585
para
Parameters can be marked as literalIN/ (the default),
! literalOUT/, literalINOUT/, or literalVARIADIC/literal.
! An literalINOUT/
parameter serves as both an input parameter (part of the calling
argument list) and an output parameter (part of the result record type).
/para
***
*** 805,810
--- 806,833
/screen
/para
/sect2
+
+sect2
+ titleVariadic acronymSQL/acronym Functions/title
+
+ para
+ acronymSQL/acronym functions can be declared to accept
+ variable number of arguments.
+ screen
+ CREATE FUNCTION mleast(variadic numeric[]) RETURNS numeric AS $$
+ SELECT min($1[i])
+FROM generate_subscripts($1,1) g(i);
+ $$ LANGUAGE SQL;
+
+ SELECT mleast(10, -1, 5, 4);
+ mleast
+
+ -1
+ (1 row)
+ /screen
+ /para
+/sect2
+
/sect1
sect1 id=xfunc-overload
*** ./src/backend/catalog/namespace.c.orig 2008-06-24 11:24:34.0 +0200
--- ./src/backend/catalog/namespace.c 2008-06-24 13:58:31.0 +0200
***
*** 570,576
* identical entries in later namespaces.
*/
FuncCandidateList
! FuncnameGetCandidates(List *names, int nargs)
{
FuncCandidateList resultList = NULL;
char *schemaname;
--- 570,576
* identical entries in later namespaces.
*/
FuncCandidateList
! FuncnameGetCandidates(List *names, int nargs, bool transform_variadic)
{
FuncCandidateList resultList = NULL;
char *schemaname;
***
*** 606,614
int pronargs = procform-pronargs;
int pathpos = 0;
FuncCandidateList newResult;
/* Ignore if it doesn't match requested argument count */
! if (nargs = 0 pronargs != nargs)
continue;
if (OidIsValid(namespaceId))
--- 606,655
int pronargs = procform-pronargs;
int pathpos = 0;
FuncCandidateList newResult;
+ Oid va_oid = InvalidOid;
+ bool variadic = false;
+ bool isnull;
+ Datum proargmodes;
+
+ /*
+ * Search type of variadic argument,
+ */
+ proargmodes = SysCacheGetAttr(PROCOID, proctup,
+ Anum_pg_proc_proargmodes, isnull);
+ if (!isnull)
+ {
+ ArrayType *ar = DatumGetArrayTypeP(proargmodes);
+ char *argmodes;
+ int j;
+
+ argmodes = ARR_DATA_PTR(ar);
+ for (j = 0; j ARR_DIMS(ar)[0]; j++)
+ if (argmodes[j
Re: [PATCHES] variadic function support
2008/6/24 Andrew Dunstan [EMAIL PROTECTED]: Tom Lane wrote: Your point about the syntax is good though. It would be better if the syntax were like create function foo (a text, variadic b int[]) or maybe even better create function foo (a text, variadic b int) since (a) this makes it much more obvious to the reader what the function might match, and (b) it leaves the door open for marking multiple parameters as variadic, if we can figure out what that means. Yes. I understand from the family Java expert that (surface syntax issues aside) the second is similar to the way Java does this, in fact, so there's some precedent. That would mean that your first would actually mean each variadic arg has to be an array of ints, which we might well want to provide for. So with that modification I'll be lots happier with the feature. I don't see problem with your syntax. It well block combination OUT and VARIADIC parameter - my one request, variadic parameter have to be array. It's more consistent with following procedure implementation - inside procedures is really array. sample: CREATE OR REPLACE least(varidic values numeric[]) -- ARRAY RETURNS numeric AS $$ SELECT $1[i] -- ARRAY FROM Regards Pavel Stehule p.s. with one exception any, because there isn't possible array from any cheers andrew -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] variadic function support
2008/6/23 Andrew Dunstan [EMAIL PROTECTED]: And what about a function that takes 2 arrays as arguments? only last argument is evaluated as variadic so function create or replace function foo(a int[], b int[]) ... variadic is called select foo(array[1,2,3], 1,2,3,4,5,6) This proposal strikes me as half-baked. Either we need proper and full support for variadic functions, or we don't, but I don't think we need syntactic sugar like the above (or maybe in this case it's really syntactic saccharine). there is some functions like Oracle's least,greater, decode that needs this feature. So I can write wrappers. For me most important are new possibility for C procedures. All this work is related to my JSON support proposal. Pavel cheers andrew -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] SQL: table function support
2008/6/10 Tom Lane [EMAIL PROTECTED]: Neil Conway [EMAIL PROTECTED] writes: On Tue, 2008-06-03 at 13:03 +0200, Pavel Stehule wrote: this patch add support of table functions syntax like ANSI SQL 2003. I'm not necessarily opposed to this, but I wonder if we really need *more* syntax variants for declaring set-returning functions. I've been saying right along that we don't. The proposed patch adds no measurable new functionality; its only reason to live is standards compliance, and I'm not convinced that's worth the confusion. Our implementation of functions is (and always will be) far enough away from the standard that notational issues like this are hardly the top of the problem list for someone wishing to import a spec-compliant function. a) current syntax is strange for beginers (and I am sure - isn't nice) - look to mailing lists. I belive so ansi syntax is better. b) it's needed for well SQL/PSM support. With table functions and RETURN QUERY we are neer standard. (It's also worth asking where the import is coming from. Who implements the spec syntax anyway? DB2 maybe, but when was the last time we heard from anyone trying to migrate from DB2 to PG?) lot of smaller new databases respect ANSI SQL 200x well - not only db2 regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] SQL: table function support
2008/6/10 Neil Conway [EMAIL PROTECTED]: On Tue, 2008-06-03 at 13:03 +0200, Pavel Stehule wrote: this patch add support of table functions syntax like ANSI SQL 2003. I'm not necessarily opposed to this, but I wonder if we really need *more* syntax variants for declaring set-returning functions. The existing patchwork of features is confusing enough as it is... internally is table functions implemenation identical with SRF. Semantically is far - user's doesn't specify return type (what is from PostgreSQL), but specifies return table, what is more natural. What more - for users is transparent chaotic joice betwen SETOF RECORD for multicolumns sets and SETOF type. Pavel -Neil -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
[PATCHES] SQL: table function support
Hello
this patch add support of table functions syntax like ANSI SQL 2003.
CREATE OR REPLACE FUNCTION foo_sql(integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
SELECT i, i+1, i+2
FROM generate_series(1, $1) g(i);
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION foo_plpgsql1(m integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
DECLARE r record;
BEGIN
FOR i IN 1..m LOOP
r = ROW(i, i+1, i+2);
RETURN NEXT r;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION foo_plpgsql2(m integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
DECLARE r record;
BEGIN
RETURN QUERY
SELECT i, i+1, i+2
FROM generate_series(1, m) g(i);
RETURN;
END;
$$ LANGUAGE plpgsql;
There are one significant difference to SRF with OUT variables.
Attributies declared in TABLE clause doesn't create local variables.
It's in conformance with SQL/PSM.
Regards
Pavel Stehule
*** ./doc/src/sgml/ref/create_function.sgml.orig 2007-09-11 02:06:41.0 +0200
--- ./doc/src/sgml/ref/create_function.sgml 2008-06-03 11:50:25.0 +0200
***
*** 21,27
synopsis
CREATE [ OR REPLACE ] FUNCTION
replaceable class=parametername/replaceable ( [ [ replaceable class=parameterargmode/replaceable ] [ replaceable class=parameterargname/replaceable ] replaceable class=parameterargtype/replaceable [, ...] ] )
! [ RETURNS replaceable class=parameterrettype/replaceable ]
{ LANGUAGE replaceable class=parameterlangname/replaceable
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
--- 21,28
synopsis
CREATE [ OR REPLACE ] FUNCTION
replaceable class=parametername/replaceable ( [ [ replaceable class=parameterargmode/replaceable ] [ replaceable class=parameterargname/replaceable ] replaceable class=parameterargtype/replaceable [, ...] ] )
! [ RETURNS replaceable class=parameterrettype/replaceable
! | RETURNS TABLE ( replaceable class=parametercolname/replaceable replaceable class=parametercoltype/replaceable [, ...] ) ]
{ LANGUAGE replaceable class=parameterlangname/replaceable
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
***
*** 410,415
--- 411,450
/listitem
/varlistentry
+ varlistentry
+ termreplaceable class=parametercolname/replaceable/term
+
+ listitem
+ para
+The name of an output table column.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
+ termreplaceable class=parametercoltype/replaceable/term
+
+ listitem
+ para
+The data type(s) of output table column.
+ /para
+ para
+Depending on the implementation language it might also be allowed
+to specify quotepseudotypes/ such as typecstring/.
+Pseudotypes indicate that the actual argument type is either
+incompletely specified, or outside the set of ordinary SQL data types.
+ /para
+ para
+The type of a column is referenced by writing
+literalreplaceable
+class=parametertablename/replaceable.replaceable
+class=parametercolumnname/replaceable%TYPE/literal.
+Using this feature can sometimes help make a function independent of
+changes to the definition of a table.
+ /para
+ /listitem
+ /varlistentry
+
/variablelist
/refsect1
***
*** 616,622
A commandCREATE FUNCTION/command command is defined in SQL:1999 and later.
The productnamePostgreSQL/productname version is similar but
not fully compatible. The attributes are not portable, neither are the
!different available languages.
/para
para
--- 651,657
A commandCREATE FUNCTION/command command is defined in SQL:1999 and later.
The productnamePostgreSQL/productname version is similar but
not fully compatible. The attributes are not portable, neither are the
!different available languages. TABLE functions are defined in SQL:2003.
/para
para
*** ./doc/src/sgml/xfunc.sgml.orig 2007-11-10 21:14:36.0 +0100
--- ./doc/src/sgml/xfunc.sgml 2008-06-03 11:50:25.0 +0200
***
*** 102,107
--- 102,115
/para
para
+ indextermprimaryTABLE/seealsofunction//An SQL function can
+ declared to return table specified by function's retun table as
+ literalTABLE(replaceablesomecolumns/)/literal. In this case
+ all rows of the last query's result are returned. Furteher details
+ appear bellow.
+ /para
+
+para
The body of an SQL function must be a list of SQL
statements separated by semicolons. A semicolon after the last
statement is optional. Unless the function is declared to return
***
*** 729,734
--- 737,800
/sect2
sect2
+ titleacronymSQL/acronym Table Functions/title
+
+ para
Re: [PATCHES] options for RAISE statement
I am sent two less dependend patch (both modify same files): COPY and RAISE USING. I am sorry, but I can't to know what commiters will be apply first. Problem is mainly in regress files because I append regress test on end of files. But boths are really generated from current HEAD. Regards Pavel Stehule 2008/5/12 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: I am sending enhanced version of original patch. Hmm ... this patch seems to have been generated against something significantly different from HEAD ... was that intentional? patching file plpgsql.sgml Hunk #1 succeeded at 2102 (offset -82 lines). Hunk #3 succeeded at 2167 (offset -82 lines). Hunk #5 succeeded at 2807 (offset -82 lines). patching file gram.y Hunk #1 succeeded at 52 (offset -1 lines). Hunk #2 succeeded at 141 with fuzz 2 (offset -2 lines). Hunk #3 succeeded at 1262 (offset -45 lines). Hunk #4 succeeded at 1314 (offset -2 lines). Hunk #5 succeeded at 1279 (offset -45 lines). Hunk #6 succeeded at 1646 (offset -2 lines). Hunk #7 succeeded at 2703 (offset -144 lines). patching file pl_comp.c Hunk #1 succeeded at 1750 (offset -1 lines). patching file pl_exec.c Hunk #1 succeeded at 2270 (offset -97 lines). patching file pl_funcs.c Hunk #1 succeeded at 1012 (offset -43 lines). patching file plpgsql.h Hunk #1 succeeded at 120 (offset -1 lines). Hunk #2 succeeded at 554 (offset -18 lines). Hunk #3 succeeded at 808 (offset -1 lines). patching file plpgsql.out Hunk #1 FAILED at 3385. 1 out of 1 hunk FAILED -- saving rejects to file plpgsql.out.rej patching file plpgsql.sql Hunk #1 FAILED at 2735. 1 out of 1 hunk FAILED -- saving rejects to file plpgsql.sql.rej regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] options for RAISE statement
Hello I am sending enhanced version of original patch. 2008/5/5 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: this patch adds possibility to set additional options (SQLSTATE, DETAIL, DETAIL_LOG and HINT) for RAISE statement, I looked this over briefly. A couple of comments: * Raising errors via hard-coded SQLSTATEs seems pretty unfriendly, at least for cases where we are reporting built-in errors. Wouldn't it be better to be able to raise errors using the same SQLSTATE names that are recognized in EXCEPTION clauses? There are new attribut CONDITION - all defined condition are possible without duplicit names and category conditions. example: RAISE NOTICE 'custom unique violation' USING (CONDITION = 'unique_violation'); * If we are going to let people throw random SQLSTATEs, there had better be a way to name those same SQLSTATEs in EXCEPTION. we can trap EXCEPTION defined via SQLSTATE now: EXCEPTION WHEN SQLSTATE 22001 THEN ... * I don't really like exposing DETAIL_LOG in this. That was a spur of the moment addition and we might take it out again; I think it's way premature to set it in stone by exposing it as a plpgsql feature. removed * Please avoid using errstart() directly. This is unwarranted intimacy with elog.h's implementation and I also think it will have unpleasant behavior if an error occurs while evaluating the RAISE arguments. (In fact, I think a user could easily force a backend PANIC that way.) The approved way to deal with ereport options that might not be there is like this: ereport(ERROR, ( ..., have_sqlstate ? errcode(...) : 0, ... That is, you should evaluate all the options into local variables and then do one normal ereport call. changed * // comments are against our coding conventions. regards, tom lane removed Regards Pavel Stehule *** ./doc/src/sgml/plpgsql.sgml.orig 2008-05-06 11:05:05.0 +0200 --- ./doc/src/sgml/plpgsql.sgml 2008-05-10 01:09:54.0 +0200 *** *** 2184,2192 --- 2184,2197 WHEN replaceablecondition/replaceable optional OR replaceablecondition/replaceable ... /optional THEN replaceablehandler_statements/replaceable optional WHEN replaceablecondition/replaceable optional OR replaceablecondition/replaceable ... /optional THEN + replaceablehandler_statements/replaceable /optional + optional WHEN replaceableSQLSTATE x/replaceable optional OR replaceableSQLSTATE x/replaceable ... /optional THEN + replaceablehandler_statements/replaceable /optional + optional WHEN replaceableSQLSTATE x/replaceable optional OR replaceablecondition/replaceable ... /optional THEN replaceablehandler_statements/replaceable ... /optional END; + /synopsis /para *** *** 2215,2221 condition name literalOTHERS/ matches every error type except literalQUERY_CANCELED/. (It is possible, but often unwise, to trap literalQUERY_CANCELED/ by name.) Condition names are ! not case-sensitive. /para para --- 2220,2227 condition name literalOTHERS/ matches every error type except literalQUERY_CANCELED/. (It is possible, but often unwise, to trap literalQUERY_CANCELED/ by name.) Condition names are ! not case-sensitive. Any condition can be subtituted by SQLSTATE ! value. /para para *** *** 2243,2248 --- 2249,2262 RAISE NOTICE 'caught division_by_zero'; RETURN x; END; + + ... + -- or same with SQLSTATE specification + EXCEPTION + WHEN SQLSTATE 22012 THEN + RAISE NOTICE 'caught division_by_zero'; + RETURN x; + END; /programlisting When control reaches the assignment to literaly/, it will *** *** 2832,2838 raise errors. synopsis ! RAISE replaceable class=parameterlevel/replaceable 'replaceable class=parameterformat/replaceable' optional, replaceable class=parameterexpression/replaceable optional, .../optional/optional; /synopsis Possible levels are literalDEBUG/literal, --- 2846,2852 raise errors. synopsis ! RAISE replaceable class=parameterlevel/replaceable 'replaceable class=parameterformat/replaceable' optional, replaceable class=parameterexpression/replaceable optional, .../optional/optional optional USING ( replaceable class=parameteroption/replaceable = replaceable class=parameterexpression/replaceable optional, ... /optional ) /optional; /synopsis Possible levels are literalDEBUG/literal, *** *** 2875,2891 para This example will abort the transaction with the given error message: programlisting ! RAISE EXCEPTION 'Nonexistent ID -- %', user_id; /programlisting /para para ! commandRAISE
Re: [PATCHES] plpgsql CASE statement - last version
Hello I am sending little bit smarter version - without redundant parsing. When test expression is defined, then expressions in WHEN part are modified like $x in ( origin_expression ) $x is referenced to invisible *case* variable that carries result of test expression. Regards Pavel Stehule 2008/5/3 Pavel Stehule [EMAIL PROTECTED]: Hello 2008/5/3 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: 2008/5/2 Heikki Linnakangas [EMAIL PROTECTED]: How about taking a completely different strategy, and implement the CASE-WHEN construct fully natively in plpgsql, instead of trying to convert it to a single SQL CASE-WHEN expression? It's not a very good match anyway; It was first variant. It's simpler for parsing and slower for execution :(. It means more than once expression evaluation and for simple case value casting and comparation. I agree with Heikki: this patch is seriously ugly, and slower for execution isn't a good enough reason for saddling us with having to maintain such a kluge in the parser. I don't really see why you should need to have multiple expression evaluations, anyhow. Can't you evaluate the test expression once and inject its value into the comparisons using CaseTestExpr, the same way the core CASE-expression code works? I have to look on this way. Regards Pavel Stehule regards, tom lane *** ./doc/src/sgml/plpgsql.sgml.orig 2008-05-03 02:11:36.0 +0200 --- ./doc/src/sgml/plpgsql.sgml 2008-05-06 11:05:05.0 +0200 *** *** 1601,1606 --- 1601,1622 paraliteralIF ... THEN ... ELSEIF ... THEN ... ELSE// /listitem /itemizedlist + + and four forms of literalCASE/: + itemizedlist + listitem + paraliteralCASE ... WHEN ... THEN ... END CASE// + /listitem + listitem + paraliteralCASE ... WHEN ... THEN ... ELSE ... END CASE// + /listitem + listitem + paraliteralCASE WHEN ... THEN ... END CASE// + /listitem + listitem + paraliteralCASE WHEN ... THEN ... ELSE ... END CASE// + /listitem + /itemizedlist /para sect3 *** *** 1751,1756 --- 1767,1838 literalELSEIF/ is an alias for literalELSIF/. /para /sect3 + + sect3 + titleSimple literalCASE/ statement/title + synopsis + CASE replaceableexpression/replaceable + WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN + replaceablestatements/replaceable + optional WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN + replaceablestatements/replaceable + optional WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN + replaceablestatements/replaceable + ... /optional/optional + optional ELSE + replaceablestatements/replaceable /optional + END CASE; + /synopsis + para + Provide conditional execution based on equality of operands. If no case is matched, + then is ELSE clause executed. If statement doesn't contains ELSE clause, + then literalCASE_NOT_FOUND/literal exception is raised. + /para + paraHere is example: + programlisting + CASE a + WHEN 1, 2 THEN + msg := 'one or two'; + ELSE + msg := 'other value than one or two'; + END CASE; + /programlisting + /para + /sect3 + + sect3 + titleSearched literalCASE/ statement/title + synopsis + CASE + WHEN replaceableboolean-expression/replaceable THEN + replaceablestatements/replaceable + optional WHEN replaceableboolean-expression/replaceable THEN + replaceablestatements/replaceable + optional WHEN replaceableboolean-expression/replaceable THEN + replaceablestatements/replaceable + ... /optional/optional + optional ELSE + replaceablestatements/replaceable /optional + END CASE; + /synopsis + para + Provide conditional execution based on truth of + replaceableboolean-expression/replaceable. If no case is matched, + then is ELSE clause executed. If statement doesn't contains ELSE clause, + then literalCASE_NOT_FOUND/literal exception is raised. + /para + para Here is example: + programlisting + CASE + WHEN a BETWEEN 0 AND 10 THEN + msg := 'value is between zero and ten'; + WHEN a BETWEEN 11 AND 20 THEN + msg := 'value is between eleven and twenty'; + END CASE; + /programlisting + /para + + /sect3 /sect2 sect2 id=plpgsql-control-structures-loops *** ./src/backend/catalog/sql_feature_packages.txt.orig 2008-05-06 11:01:18.0 +0200 --- ./src/backend/catalog/sql_feature_packages.txt 2008-05-06 11:05:05.0 +0200 *** *** 41,46 --- 41,48 F671 Enhanced integrity management F701 Enhanced integrity
Re: [PATCHES] options for RAISE statement
Hello I thing, all your comments are not problem. I'll send new version this week. Thank You Pavel Stehule 2008/5/5 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: this patch adds possibility to set additional options (SQLSTATE, DETAIL, DETAIL_LOG and HINT) for RAISE statement, I looked this over briefly. A couple of comments: * Raising errors via hard-coded SQLSTATEs seems pretty unfriendly, at least for cases where we are reporting built-in errors. Wouldn't it be better to be able to raise errors using the same SQLSTATE names that are recognized in EXCEPTION clauses? * If we are going to let people throw random SQLSTATEs, there had better be a way to name those same SQLSTATEs in EXCEPTION. * I don't really like exposing DETAIL_LOG in this. That was a spur of the moment addition and we might take it out again; I think it's way premature to set it in stone by exposing it as a plpgsql feature. * Please avoid using errstart() directly. This is unwarranted intimacy with elog.h's implementation and I also think it will have unpleasant behavior if an error occurs while evaluating the RAISE arguments. (In fact, I think a user could easily force a backend PANIC that way.) The approved way to deal with ereport options that might not be there is like this: ereport(ERROR, ( ..., have_sqlstate ? errcode(...) : 0, ... That is, you should evaluate all the options into local variables and then do one normal ereport call. * // comments are against our coding conventions. regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] plpgsql CASE statement - last version
Hello 2008/5/3 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: 2008/5/2 Heikki Linnakangas [EMAIL PROTECTED]: How about taking a completely different strategy, and implement the CASE-WHEN construct fully natively in plpgsql, instead of trying to convert it to a single SQL CASE-WHEN expression? It's not a very good match anyway; It was first variant. It's simpler for parsing and slower for execution :(. It means more than once expression evaluation and for simple case value casting and comparation. I agree with Heikki: this patch is seriously ugly, and slower for execution isn't a good enough reason for saddling us with having to maintain such a kluge in the parser. I don't really see why you should need to have multiple expression evaluations, anyhow. Can't you evaluate the test expression once and inject its value into the comparisons using CaseTestExpr, the same way the core CASE-expression code works? I have to look on this way. Regards Pavel Stehule regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] plpgsql CASE statement - last version
Hello 2008/5/2 Heikki Linnakangas [EMAIL PROTECTED]: Pavel Stehule wrote: Hello I found some bugs when I used base_lexer, so I returned back own lexer. It's only little bit longer, but simpler. Hmm. I don't like having to lex the expressions again. It just doesn't feel right. me too. But when I tested to use base_lexer routines, I had to write similar number of lines and add new separate file http://archives.postgresql.org/pgsql-patches/2008-04/msg00131.php - main problems of this version is enhanced strings, because base_lexer don't forward this information. How about taking a completely different strategy, and implement the CASE-WHEN construct fully natively in plpgsql, instead of trying to convert it to a single SQL CASE-WHEN expression? It's not a very good match anyway; you have to do tricks to convert the comma-separated lists of WHEN expressions to WHEN-THEN clauses, and you can't use the THEN-clauses as is, but you have to replace them with offsets into the array. I think implementing the logic in pl_exec.c would lead to cleaner code. It was first variant. It's simpler for parsing and slower for execution :(. It means more than once expression evaluation and for simple case value casting and comparation. FWIW, the current approach gives pretty cryptic CONTEXT information in error messages as well. For example, this pretty simple case-when example: postgres=# create or replace FUNCTION case_test(int) returns text as $$ begin case $1 when 1 then return 'one'; when 'invalid' then return 'two'; when 3,4,5 then return 'three, four or five'; end case; end; $$ language plpgsql immutable; CREATE FUNCTION gives this pretty hard-to-understand error message: postgres=# SELECT case_test(1); ERROR: invalid input syntax for integer: invalid CONTEXT: SQL statement SELECT CASE $1 WHEN 1 THEN 1 WHEN 'invalid' THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 3 WHEN 5 THEN 3 END PL/pgSQL function case_test line 2 at unknown BTW, what does PL/SQL or PSM say about the type-compatibility of the CASE and the WHENs? We're very lenient in assignments, how should this behave? what I know, it have to be compareable case $1 when 1 then return 'one'; when 'invalid' then return 'two'; is same as if expr1 = 1 then\ return 'one' elsif expr1 = 'invalid' then I have to agree, so this message is ugly, but on second hand I can check this error in compile time. When I move it to pl_exec I can detect this bug much later, so current solution is safe. So, when I read error message I see correct message - only CONTEXT is crazy (but it is strange not only in this case)/ I haven't idea how I can solve it - maybe notice in documentation. We can add hint to this message. Other way (mix read_sql_construct and add_expr) has other problem with nested case statements. Maybe safe to somewhere positions of placeholders. But It will not be simple too, because width of placeholders can be different. Regards Pavel Stehule -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] plpgsql CASE statement - last version
Hello
2008/5/1 Jaime Casanova [EMAIL PROTECTED]:
On Sat, Apr 5, 2008 at 6:57 AM, Pavel Stehule [EMAIL PROTECTED] wrote:
Hello
I found some bugs when I used base_lexer, so I returned back own
lexer. It's only little bit longer, but simpler.
you really compile this one? i get a complain because
read_sql_construct is called with 8 arguments and it should have only
7..
yes, I did it. 8 arguments are from EXECUTE USING patch
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/pl/plpgsql/src/gram.y.diff?r1=1.108;r2=1.109;f=h
Pavel
.
+ expr = read_sql_construct(',', K_THEN, 0, THEN,
+ SELECT , true, true, tok);
gram.y: In function 'plpgsql_yyparse':
gram.y:1697: warning: passing argument 5 of 'read_sql_construct' makes
integer from pointer without a cast
gram.y:1697: warning: passing argument 7 of 'read_sql_construct' makes
pointer from integer without a cast
gram.y:1697: error: too many arguments to function 'read_sql_construct'
--
regards,
Jaime Casanova
Soporte de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 087171157
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] temporal version of generate_series()
2008/5/1 H. Harada [EMAIL PROTECTED]:
2008/5/1 H. Harada [EMAIL PROTECTED]:
2008/5/1 Pavel Stehule [EMAIL PROTECTED]:
Hello
why you don't use polymorphic types?
Ah, good idea. I didn't think we could fix the third argument to
interval but anyelement.
For a temporal version, it's reasonable.
I was thinking about it again. There are 3 points:
a. It will get complicated in the function to resolve operator for
polymorphic types, including search for namespace and error (not
found) handling.
yes, it's true;
b. Other temporal data types than timestamp is easy to be casted from
timestamp results.
c. In the integer version of generate_series also it is possible to
cast the results to other numerical types though harder to cast them
to temporal data types.
So it would be better to keep current patch, isn't it?
I missing generator for date - casting from and to timestemp is
little bit ugly - but polymorphic types in C isn't good idea, I see
it.
Regards
Pavel Stehule
postgres=# select generate_series('2008/05/01 20:00'::timestamp,
'2008/05/02 08:00'::timestamp
, '1 hour'::interval);
generate_series
-
2008-05-01 20:00:00
2008-05-01 21:00:00
2008-05-01 22:00:00
2008-05-01 23:00:00
2008-05-02 00:00:00
2008-05-02 01:00:00
2008-05-02 02:00:00
2008-05-02 03:00:00
2008-05-02 04:00:00
2008-05-02 05:00:00
2008-05-02 06:00:00
2008-05-02 07:00:00
2008-05-02 08:00:00
(13 rows)
postgres=# select generate_series('2008/05/01 20:00'::timestamp,
'2008/05/02 08:00'::timestamp
, '1 hour'::interval)::time;
generate_series
-
20:00:00
21:00:00
22:00:00
23:00:00
00:00:00
01:00:00
02:00:00
03:00:00
04:00:00
05:00:00
06:00:00
07:00:00
08:00:00
(13 rows)
postgres=# select generate_series('2008/05/01 20:00'::timestamp,
'2008/05/02 08:00'::timestamp
, '1 hour'::interval)::timestamptz;
generate_series
2008-05-01 20:00:00+09
2008-05-01 21:00:00+09
2008-05-01 22:00:00+09
2008-05-01 23:00:00+09
2008-05-02 00:00:00+09
2008-05-02 01:00:00+09
2008-05-02 02:00:00+09
2008-05-02 03:00:00+09
2008-05-02 04:00:00+09
2008-05-02 05:00:00+09
2008-05-02 06:00:00+09
2008-05-02 07:00:00+09
2008-05-02 08:00:00+09
(13 rows)
postgres=# select generate_series('2008/05/01 20:00'::timestamp,
'2008/05/02 08:00'::timestamp
, '1 hour'::interval)::date;
generate_series
-
2008-05-01
2008-05-01
2008-05-01
2008-05-01
2008-05-02
2008-05-02
2008-05-02
2008-05-02
2008-05-02
2008-05-02
2008-05-02
2008-05-02
2008-05-02
(13 rows)
Hitoshi Harada
2008/5/1 H. Harada [EMAIL PROTECTED]:
2008/5/1 Pavel Stehule [EMAIL PROTECTED]:
Hello
why you don't use polymorphic types?
Ah, good idea. I didn't think we could fix the third argument to
interval but anyelement.
For a temporal version, it's reasonable.
Also, the name generate_time_series is better than before?
Hitoshi Harada
2008/5/1 Pavel Stehule [EMAIL PROTECTED]:
Hello
why you don't use polymorphic types?
like:
create or replace function generate_time_series(anyelement,
anyelement, interval, OUT result anyelement)
returns setof anyelement as $$
begin
result := $1;
while (result = $2) loop
return next;
result := result + $3;
end loop;
return;
end;
$$ language plpgsql;
Regards
Pavel Stehule
2008/5/1 H. Harada [EMAIL PROTECTED]:
Here's the sync and updated patch.
It contains strict in catalog as well.
Hitoshi Harada
2008/4/24 H. Harada [EMAIL PROTECTED]:
2008/4/23 Alvaro Herrera [EMAIL PROTECTED]:
H.Harada escribió:
# This is my first time to send a patch. If I did something
wrong, I
appreciate your pointing me out.
Brace positioning is off w.r.t. our conventions -- please fix
that and
resubmit.
Here's updated version. Thanks for your advice.
Hitoshi Harada
2008/4/23 Alvaro Herrera [EMAIL PROTECTED]:
H.Harada escribió:
# This is my first time to send a patch. If I did something
wrong, I
appreciate your pointing me out.
Brace positioning is off w.r.t. our conventions -- please fix
that and
resubmit.
I have added this patch to the May commitfest.
--
Alvaro Herrera
http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http
Re: [PATCHES] temporal version of generate_series()
Hello why you don't use polymorphic types? like: create or replace function generate_time_series(anyelement, anyelement, interval, OUT result anyelement) returns setof anyelement as $$ begin result := $1; while (result = $2) loop return next; result := result + $3; end loop; return; end; $$ language plpgsql; Regards Pavel Stehule 2008/5/1 H. Harada [EMAIL PROTECTED]: Here's the sync and updated patch. It contains strict in catalog as well. Hitoshi Harada 2008/4/24 H. Harada [EMAIL PROTECTED]: 2008/4/23 Alvaro Herrera [EMAIL PROTECTED]: H.Harada escribió: # This is my first time to send a patch. If I did something wrong, I appreciate your pointing me out. Brace positioning is off w.r.t. our conventions -- please fix that and resubmit. Here's updated version. Thanks for your advice. Hitoshi Harada 2008/4/23 Alvaro Herrera [EMAIL PROTECTED]: H.Harada escribió: # This is my first time to send a patch. If I did something wrong, I appreciate your pointing me out. Brace positioning is off w.r.t. our conventions -- please fix that and resubmit. I have added this patch to the May commitfest. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
[PATCHES] options for RAISE statement
Hello
this patch adds possibility to set additional options (SQLSTATE,
DETAIL, DETAIL_LOG and HINT) for RAISE statement,
Proposal: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00919.php
I changed keyword from WITH to USING, because I don't would to create
new keyword
RAISE level 'format' [, expression [, ...]] [ USING ( option =
expression [, ... ] ) ];
RAISE EXCEPTION 'Nonexistent ID -- %', user_id
USING (hint = 'Please, check your user id');
Regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-04-16 11:17:51.0 +0200
--- ./doc/src/sgml/plpgsql.sgml 2008-04-16 12:59:44.0 +0200
***
*** 2742,2748
raise errors.
synopsis
! RAISE replaceable class=parameterlevel/replaceable 'replaceable class=parameterformat/replaceable' optional, replaceable class=parameterexpression/replaceable optional, .../optional/optional;
/synopsis
Possible levels are literalDEBUG/literal,
--- 2742,2748
raise errors.
synopsis
! RAISE replaceable class=parameterlevel/replaceable 'replaceable class=parameterformat/replaceable' optional, replaceable class=parameterexpression/replaceable optional, .../optional/optional optional USING ( replaceable class=parameteroption/replaceable = replaceable class=parameterexpression/replaceable optional, ... /optional ) /optional;
/synopsis
Possible levels are literalDEBUG/literal,
***
*** 2785,2801
para
This example will abort the transaction with the given error message:
programlisting
! RAISE EXCEPTION 'Nonexistent ID -- %', user_id;
/programlisting
/para
para
! commandRAISE EXCEPTION/command presently always generates
! the same varnameSQLSTATE/varname code, literalP0001/, no matter what message
it is invoked with. It is possible to trap this exception with
literalEXCEPTION ... WHEN RAISE_EXCEPTION THEN .../ but there
is no way to tell one commandRAISE/ from another.
/para
/sect1
sect1 id=plpgsql-trigger
--- 2785,2808
para
This example will abort the transaction with the given error message:
programlisting
! RAISE EXCEPTION 'Nonexistent ID -- %', user_id USING (hint = 'Please, check your user id');
/programlisting
/para
para
! commandRAISE EXCEPTION/command presently generates
! the same varnameSQLSTATE/varname code, literalP0001/ , no matter what message
it is invoked with. It is possible to trap this exception with
literalEXCEPTION ... WHEN RAISE_EXCEPTION THEN .../ but there
is no way to tell one commandRAISE/ from another.
/para
+
+ para
+ With additional options is possible set some log informaition related to
+ raised exception. Possible options are literalSQLSTATE/literal,
+ literalDETAIL/literal, literalDETAIL_LOG/literal and literalHINT/literal.
+ /para
+
/sect1
sect1 id=plpgsql-trigger
*** ./src/pl/plpgsql/src/gram.y.orig 2008-04-15 07:37:03.0 +0200
--- ./src/pl/plpgsql/src/gram.y 2008-04-15 13:42:36.0 +0200
***
*** 52,57
--- 52,58
const char *end_label);
static PLpgSQL_expr *read_cursor_args(PLpgSQL_var *cursor,
int until, const char *expected);
+ static List *read_exception_additional_options();
%}
***
*** 1258,1263
--- 1259,1265
new-elog_level = $3;
new-message = $4;
new-params = NIL;
+ new-options = NIL;
tok = yylex();
***
*** 1266,1272
* indicates no parameters, or a comma that
* begins the list of parameter expressions
*/
! if (tok != ',' tok != ';')
yyerror(syntax error);
if (tok == ',')
--- 1268,1274
* indicates no parameters, or a comma that
* begins the list of parameter expressions
*/
! if (tok != ',' tok != ';' tok != K_USING)
yyerror(syntax error);
if (tok == ',')
***
*** 1274,1286
do
{
PLpgSQL_expr *expr;
!
! expr = read_sql_expression2(',', ';',
! , or ;,
! tok);
new-params = lappend(new-params, expr);
} while (tok == ',');
}
$$ = (PLpgSQL_stmt *)new;
}
--- 1276,1294
do
{
PLpgSQL_expr *expr;
!
! expr = read_sql_construct(',', ';', K_USING, , or ; or USING,
! SELECT , true, true, tok);
new-params = lappend(new-params, expr);
} while (tok == ',');
}
+
+ if (tok == K_USING)
+ {
+ new-options = read_exception_additional_options();
+ if (yylex() != ';')
+ yyerror(syntax error);
+ }
$$ = (PLpgSQL_stmt *)new;
}
***
*** 2633,2638
--- 2641,2704
}
+ /*
+ * Procedure read RAISE
[PATCHES] plpgsql CASE statement - last version
Hello
I found some bugs when I used base_lexer, so I returned back own
lexer. It's only little bit longer, but simpler.
Regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-04-04 12:07:12.0 +0200
--- ./doc/src/sgml/plpgsql.sgml 2008-04-04 21:55:08.0 +0200
***
*** 1590,1595
--- 1590,1611
paraliteralIF ... THEN ... ELSEIF ... THEN ... ELSE//
/listitem
/itemizedlist
+
+ and four forms of literalCASE/:
+ itemizedlist
+ listitem
+ paraliteralCASE ... WHEN ... THEN ... END CASE//
+ /listitem
+ listitem
+ paraliteralCASE ... WHEN ... THEN ... ELSE ... END CASE//
+ /listitem
+ listitem
+ paraliteralCASE WHEN ... THEN ... END CASE//
+ /listitem
+ listitem
+ paraliteralCASE WHEN ... THEN ... ELSE ... END CASE//
+ /listitem
+ /itemizedlist
/para
sect3
***
*** 1740,1745
--- 1756,1827
literalELSEIF/ is an alias for literalELSIF/.
/para
/sect3
+
+ sect3
+ titleSimple literalCASE/ statement/title
+ synopsis
+ CASE replaceableexpression/replaceable
+ WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN
+ replaceablestatements/replaceable
+ optional WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN
+ replaceablestatements/replaceable
+ optional WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN
+ replaceablestatements/replaceable
+ ... /optional/optional
+ optional ELSE
+ replaceablestatements/replaceable /optional
+ END CASE;
+ /synopsis
+ para
+ Provide conditional execution based on equality of operands. If no case is matched,
+ then is ELSE clause executed. If statement doesn't contains ELSE clause,
+ then literalCASE_NOT_FOUND/literal exception is raised.
+ /para
+ paraHere is example:
+ programlisting
+ CASE a
+ WHEN 1, 2 THEN
+ msg := 'one or two';
+ ELSE
+ msg := 'other value than one or two';
+ END CASE;
+ /programlisting
+ /para
+ /sect3
+
+ sect3
+ titleSearched literalCASE/ statement/title
+ synopsis
+ CASE
+ WHEN replaceableboolean-expression/replaceable THEN
+ replaceablestatements/replaceable
+ optional WHEN replaceableboolean-expression/replaceable THEN
+ replaceablestatements/replaceable
+ optional WHEN replaceableboolean-expression/replaceable THEN
+ replaceablestatements/replaceable
+ ... /optional/optional
+ optional ELSE
+ replaceablestatements/replaceable /optional
+ END CASE;
+ /synopsis
+ para
+ Provide conditional execution based on truth of
+ replaceableboolean-expression/replaceable. If no case is matched,
+ then is ELSE clause executed. If statement doesn't contains ELSE clause,
+ then literalCASE_NOT_FOUND/literal exception is raised.
+ /para
+ para Here is example:
+ programlisting
+ CASE
+ WHEN a BETWEEN 0 AND 10 THEN
+ msg := 'value is between zero and ten';
+ WHEN a BETWEEN 11 AND 20 THEN
+ msg := 'value is between eleven and twenty';
+ END CASE;
+ /programlisting
+ /para
+
+ /sect3
/sect2
sect2 id=plpgsql-control-structures-loops
*** ./src/backend/catalog/sql_feature_packages.txt.orig 2008-04-04 22:47:55.0 +0200
--- ./src/backend/catalog/sql_feature_packages.txt 2008-04-04 22:59:55.0 +0200
***
*** 41,46
--- 41,48
F671 Enhanced integrity management
F701 Enhanced integrity management
F812 Core
+ P004 PSM
+ P008 PSM
S011 Core
S023 Basic object support
S024 Enhanced object support
*** ./src/backend/catalog/sql_features.txt.orig 2008-04-04 22:45:52.0 +0200
--- ./src/backend/catalog/sql_features.txt 2008-04-04 23:05:31.0 +0200
***
*** 297,302
--- 297,304
F831 Full cursor update NO
F831 Full cursor update 01 Updatable scrollable cursors NO
F831 Full cursor update 02 Updatable ordered cursors NO
+ P004 Extended CASE statement YES
+ P008 Comma-separated predicates in simple CASE statement YES
S011 Distinct data types NO
S011 Distinct data types 01 USER_DEFINED_TYPES view NO
S023 Basic structured types NO
*** ./src/include/utils/errcodes.h.orig 2008-04-02 14:02:06.0 +0200
--- ./src/include/utils/errcodes.h 2008-04-03 07:49:40.0 +0200
***
*** 107,112
--- 107,113
/* Class 22 - Data Exception */
#define ERRCODE_DATA_EXCEPTIONMAKE_SQLSTATE('2','2', '0','0','0')
+ #define ERRCODE_CASE_NOT_FOUNDERRCODE_DATA_EXCEPTION
#define ERRCODE_ARRAY_ELEMENT_ERROR MAKE_SQLSTATE('2','2', '0','2','E')
/* SQL99's actual definition of array element error is subscript error */
#define
[PATCHES] plpgsql RETURN QUERY EXECUTE
Hello This patch allows dynamic queries in RETURN QUERY statement. http://archives.postgresql.org/pgsql-hackers/2008-02/msg01180.php Sample: CREATE OR REPLACE FUNCTION test(tab varchar, b integer) RETURNS SETOF integer AS $$ BEGIN RETURN QUERY EXECUTE 'SELECT a FROM ' || tab 'WHERE b = $1' USING b; RETURN; END; $$ LANGUAGE plpgsql; Regards Pavel Stehule *** ./doc/src/sgml/plpgsql.sgml.orig 2008-04-05 12:19:24.0 +0200 --- ./doc/src/sgml/plpgsql.sgml 2008-04-05 12:32:51.0 +0200 *** *** 1451,1457 /sect3 sect3 ! titlecommandRETURN NEXT/ and commandRETURN QUERY/command/title indexterm primaryRETURN NEXT/primary secondaryin PL/PgSQL/secondary --- 1451,1457 /sect3 sect3 ! titlecommandRETURN NEXT/, commandRETURN QUERY/command, commandRETURN QUERY EXECUTE/command/title indexterm primaryRETURN NEXT/primary secondaryin PL/PgSQL/secondary *** *** 1460,1469 --- 1460,1474 primaryRETURN QUERY/primary secondaryin PL/PgSQL/secondary /indexterm + indexterm + primaryRETURN QUERY EXECUTE/primary + secondaryin PL/PgSQL/secondary + /indexterm synopsis RETURN NEXT replaceableexpression/replaceable; RETURN QUERY replaceablequery/replaceable; + RETURN QUERY EXECUTE replaceable class=commandcommand-string/replaceable optional USING replaceableexpression/replaceable optional, .../optional /optional; /synopsis para *** *** 1471,1477 literalSETOF replaceablesometype//literal, the procedure to follow is slightly different. In that case, the individual items to return are specified by a sequence of commandRETURN ! NEXT/command or commandRETURN QUERY/command commands, and then a final commandRETURN/command command with no argument is used to indicate that the function has finished executing. commandRETURN NEXT/command can be used with both scalar and --- 1476,1483 literalSETOF replaceablesometype//literal, the procedure to follow is slightly different. In that case, the individual items to return are specified by a sequence of commandRETURN ! NEXT/command, commandRETURN QUERY/command commands, or ! commandRETURN QUERY EXECUTE/command and then a final commandRETURN/command command with no argument is used to indicate that the function has finished executing. commandRETURN NEXT/command can be used with both scalar and *** *** 1479,1497 quotetable/quote of results will be returned. commandRETURN QUERY/command appends the results of executing a query to the function's result set. commandRETURN ! NEXT/command and commandRETURN QUERY/command can be freely intermixed in a single set-returning function, in which case their results will be concatenated. /para para ! commandRETURN NEXT/command and commandRETURN ! QUERY/command do not actually return from the function mdash; they simply append zero or more rows to the function's result set. Execution then continues with the next statement in the applicationPL/pgSQL/ function. As successive commandRETURN NEXT/command or commandRETURN ! QUERY/command commands are executed, the result set is built up. A final commandRETURN/command, which should have no argument, causes control to exit the function (or you can just let control reach the end of the function). --- 1485,1506 quotetable/quote of results will be returned. commandRETURN QUERY/command appends the results of executing a query to the function's result set. commandRETURN ! NEXT/command, commandRETURN QUERY/command and ! commandRETURN QUERY EXECUTE/command can be freely intermixed in a single set-returning function, in which case their results will be concatenated. /para para ! commandRETURN NEXT/command, commandRETURN ! QUERY/command and commandRETURN QUERY EXECUTE/command ! do not actually return from the function mdash; they simply append zero or more rows to the function's result set. Execution then continues with the next statement in the applicationPL/pgSQL/ function. As successive commandRETURN NEXT/command or commandRETURN ! QUERY/command or commandRETURN QUERY EXECUTE/command ! commands are executed, the result set is built up. A final commandRETURN/command, which should have no argument, causes control to exit the function (or you can just let control reach the end of the function). *** *** 1538,1553 SELECT * FROM getallfoo(); /programlisting ! Note that functions using commandRETURN NEXT/command or ! commandRETURN QUERY/command must
[PATCHES] plpgsql CASE statement
Hello I finished this patch. Proposal: http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php It's compatible with PL/SQL (Oracle) and SQL/PSM (ANSI). CASE statements is parsed and transformed to CASE expression and statements paths. Result of CASE expression is used as index to array of statements paths. Sample: CREATE OR REPLACE FUNCTION foo(int) RETURNS void AS $$ BEGIN CASE $1 WHEN 1,2,3 THEN RAISE NOTICE '1,2'; RAISE NOTICE '3'; WHEN 4 THEN RAISE NOTICE '4'; ELSE RAISE NOTICE 'other than 1,2,3,4'; END CASE; RETURN; END; $$ LANGUAGE plpgsql; This statement is transformated to: three statement paths: [0] RAISE NOTICE 'other than 1,2,3,4'; [1] RAISE NOTICE '1,2'; RAISE NOTICE '3'; [2] RAISE NOTICE '4'; and case expression CASE $1 WHEN 1 THEN 1 WHEN 2 THEN 1 WHEN 3 THEN 1 WHEN 4 THEN 2 END; When result is NULL then it uses 0 path. Regards Pavel Stehule *** ./doc/src/sgml/features-supported.sgml.orig 2008-04-04 14:16:52.0 +0200 --- ./doc/src/sgml/features-supported.sgml 2008-04-04 21:20:46.0 +0200 *** *** 1338,1343 --- 1338,1355 entry/entry /row row + entryP004/entry + entryPSM/entry + entryExtended CASE statement/entry + entry/entry + /row + row + entryP008/entry + entryPSM/entry + entryComma-separated predicates in simple CASE statement/entry + entry/entry + /row + row entryS071/entry entryEnhanced object support/entry entrySQL paths in function and type name resolution/entry *** ./doc/src/sgml/plpgsql.sgml.orig 2008-04-04 12:07:12.0 +0200 --- ./doc/src/sgml/plpgsql.sgml 2008-04-04 21:55:08.0 +0200 *** *** 1590,1595 --- 1590,1611 paraliteralIF ... THEN ... ELSEIF ... THEN ... ELSE// /listitem /itemizedlist + + and four forms of literalCASE/: + itemizedlist + listitem + paraliteralCASE ... WHEN ... THEN ... END CASE// + /listitem + listitem + paraliteralCASE ... WHEN ... THEN ... ELSE ... END CASE// + /listitem + listitem + paraliteralCASE WHEN ... THEN ... END CASE// + /listitem + listitem + paraliteralCASE WHEN ... THEN ... ELSE ... END CASE// + /listitem + /itemizedlist /para sect3 *** *** 1740,1745 --- 1756,1827 literalELSEIF/ is an alias for literalELSIF/. /para /sect3 + + sect3 + titleSimple literalCASE/ statement/title + synopsis + CASE replaceableexpression/replaceable + WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN + replaceablestatements/replaceable + optional WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN + replaceablestatements/replaceable + optional WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN + replaceablestatements/replaceable + ... /optional/optional + optional ELSE + replaceablestatements/replaceable /optional + END CASE; + /synopsis + para + Provide conditional execution based on equality of operands. If no case is matched, + then is ELSE clause executed. If statement doesn't contains ELSE clause, + then literalCASE_NOT_FOUND/literal exception is raised. + /para + paraHere is example: + programlisting + CASE a + WHEN 1, 2 THEN + msg := 'one or two'; + ELSE + msg := 'other value than one or two'; + END CASE; + /programlisting + /para + /sect3 + + sect3 + titleSearched literalCASE/ statement/title + synopsis + CASE + WHEN replaceableboolean-expression/replaceable THEN + replaceablestatements/replaceable + optional WHEN replaceableboolean-expression/replaceable THEN + replaceablestatements/replaceable + optional WHEN replaceableboolean-expression/replaceable THEN + replaceablestatements/replaceable + ... /optional/optional + optional ELSE + replaceablestatements/replaceable /optional + END CASE; + /synopsis + para + Provide conditional execution based on truth of + replaceableboolean-expression/replaceable. If no case is matched, + then is ELSE clause executed. If statement doesn't contains ELSE clause, + then literalCASE_NOT_FOUND/literal exception is raised. + /para + para Here is example: + programlisting + CASE + WHEN a BETWEEN 0 AND 10 THEN + msg := 'value is between zero and ten'; + WHEN a BETWEEN 11 AND 20 THEN + msg := 'value is between eleven and twenty'; + END CASE; + /programlisting + /para + + /sect3 /sect2 sect2 id=plpgsql-control-structures-loops *** ./src/include/utils/errcodes.h.orig 2008-04-02 14:02:06.0 +0200 --- ./src/include/utils
[PATCHES] fixing sql generated features - plpgsql case statement
Hello
I am sorry, I sent wrong patch, I did diff from generated files.
Current patch set sql features correctly.
Regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-04-04 12:07:12.0 +0200
--- ./doc/src/sgml/plpgsql.sgml 2008-04-04 21:55:08.0 +0200
***
*** 1590,1595
--- 1590,1611
paraliteralIF ... THEN ... ELSEIF ... THEN ... ELSE//
/listitem
/itemizedlist
+
+ and four forms of literalCASE/:
+ itemizedlist
+ listitem
+ paraliteralCASE ... WHEN ... THEN ... END CASE//
+ /listitem
+ listitem
+ paraliteralCASE ... WHEN ... THEN ... ELSE ... END CASE//
+ /listitem
+ listitem
+ paraliteralCASE WHEN ... THEN ... END CASE//
+ /listitem
+ listitem
+ paraliteralCASE WHEN ... THEN ... ELSE ... END CASE//
+ /listitem
+ /itemizedlist
/para
sect3
***
*** 1740,1745
--- 1756,1827
literalELSEIF/ is an alias for literalELSIF/.
/para
/sect3
+
+ sect3
+ titleSimple literalCASE/ statement/title
+ synopsis
+ CASE replaceableexpression/replaceable
+ WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN
+ replaceablestatements/replaceable
+ optional WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN
+ replaceablestatements/replaceable
+ optional WHEN replaceableexpression/replaceable optional, replaceableexpression/replaceable optional ... /optional/optional THEN
+ replaceablestatements/replaceable
+ ... /optional/optional
+ optional ELSE
+ replaceablestatements/replaceable /optional
+ END CASE;
+ /synopsis
+ para
+ Provide conditional execution based on equality of operands. If no case is matched,
+ then is ELSE clause executed. If statement doesn't contains ELSE clause,
+ then literalCASE_NOT_FOUND/literal exception is raised.
+ /para
+ paraHere is example:
+ programlisting
+ CASE a
+ WHEN 1, 2 THEN
+ msg := 'one or two';
+ ELSE
+ msg := 'other value than one or two';
+ END CASE;
+ /programlisting
+ /para
+ /sect3
+
+ sect3
+ titleSearched literalCASE/ statement/title
+ synopsis
+ CASE
+ WHEN replaceableboolean-expression/replaceable THEN
+ replaceablestatements/replaceable
+ optional WHEN replaceableboolean-expression/replaceable THEN
+ replaceablestatements/replaceable
+ optional WHEN replaceableboolean-expression/replaceable THEN
+ replaceablestatements/replaceable
+ ... /optional/optional
+ optional ELSE
+ replaceablestatements/replaceable /optional
+ END CASE;
+ /synopsis
+ para
+ Provide conditional execution based on truth of
+ replaceableboolean-expression/replaceable. If no case is matched,
+ then is ELSE clause executed. If statement doesn't contains ELSE clause,
+ then literalCASE_NOT_FOUND/literal exception is raised.
+ /para
+ para Here is example:
+ programlisting
+ CASE
+ WHEN a BETWEEN 0 AND 10 THEN
+ msg := 'value is between zero and ten';
+ WHEN a BETWEEN 11 AND 20 THEN
+ msg := 'value is between eleven and twenty';
+ END CASE;
+ /programlisting
+ /para
+
+ /sect3
/sect2
sect2 id=plpgsql-control-structures-loops
*** ./src/backend/catalog/sql_feature_packages.txt.orig 2008-04-04 22:47:55.0 +0200
--- ./src/backend/catalog/sql_feature_packages.txt 2008-04-04 22:59:55.0 +0200
***
*** 41,46
--- 41,48
F671 Enhanced integrity management
F701 Enhanced integrity management
F812 Core
+ P004 PSM
+ P008 PSM
S011 Core
S023 Basic object support
S024 Enhanced object support
*** ./src/backend/catalog/sql_features.txt.orig 2008-04-04 22:45:52.0 +0200
--- ./src/backend/catalog/sql_features.txt 2008-04-04 23:05:31.0 +0200
***
*** 297,302
--- 297,304
F831 Full cursor update NO
F831 Full cursor update 01 Updatable scrollable cursors NO
F831 Full cursor update 02 Updatable ordered cursors NO
+ P004 Extended CASE statement YES
+ P008 Comma-separated predicates in simple CASE statement YES
S011 Distinct data types NO
S011 Distinct data types 01 USER_DEFINED_TYPES view NO
S023 Basic structured types NO
*** ./src/include/utils/errcodes.h.orig 2008-04-02 14:02:06.0 +0200
--- ./src/include/utils/errcodes.h 2008-04-03 07:49:40.0 +0200
***
*** 107,112
--- 107,113
/* Class 22 - Data Exception */
#define ERRCODE_DATA_EXCEPTIONMAKE_SQLSTATE('2','2', '0','0','0')
+ #define ERRCODE_CASE_NOT_FOUNDERRCODE_DATA_EXCEPTION
#define ERRCODE_ARRAY_ELEMENT_ERROR MAKE_SQLSTATE('2','2', '0','2','E')
/* SQL99's actual definition of array element error is subscript error */
#define
Re: [PATCHES] actualized SQL/PSM patch
Hello I'm not against having SQL/PSM support. I'm just saying I'm not willing to support two copies of plpgsql to do it. regards, tom lane I understand it well. Pending development of plpgpsm I respected unbreakability plpgsql. So I can move duplicate parts to separate files and I'll do it. I thinking about new directory structure (some like) pl/sqlsp/ .. sql Stored Procedures pl/sqlsp/utils pl/sqlsp/plpgsql - only plpgpsm code pl/sqlsp/plpgpsm - only plpgsql code Regards Pavel Stehule -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] actualized SQL/PSM patch
Hello On 01/04/2008, Tom Lane [EMAIL PROTECTED] wrote: Pavel Stehule [EMAIL PROTECTED] writes: I actualized sql/psm patch. This patch can be downloaded from http://www.pgsql.cz/patches/plpgpsm.diff.gz The fundamental problem I've got with this patch is that it adds 400K of new code (and that's just the code, not counting documentation or regression tests) that we'll have to maintain, to obtain a feature that so far as I've heard there is precisely zero demand for. The duplicativeness of the code with plpgsql doesn't make this prospect any more pleasant, either. The idea would be a lot easier to swallow if the code were refactored to avoid the duplication with plpgsql. This is long run and needs hard reorganisation of plpgsql code. And moving some plpgsql code to core. But I don't expect so plpgpsm code can be less than 200KB. Regards Pavel Stehule regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
[PATCHES] Fwd: WIP: CASE statement for PL/pgSQL
correct queue
Hello
I finished this patch.
Proposal: http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php
It's compatible with PL/SQL (Oracle) and SQL/PSM (ANSI).
CASE statements is parsed and transformed to CASE expression and
statements paths. Result of CASE expression is used as index to array
of statements paths. It's fast but I have to once time reparse SQL
queries - it generate about 150 lines code, because I need to get all
parameter's positions. It's one disadvantage. On second hand, this
statement needs only one expression evaluation.
Sample:
CREATE OR REPLACE FUNCTION foo(int)
RETURNS void AS $$
BEGIN
CASE $1
WHEN 1,2,3 THEN
RAISE NOTICE '1,2';
RAISE NOTICE '3';
WHEN 4 THEN
RAISE NOTICE '4';
ELSE
RAISE NOTICE 'other than 1,2,3,4';
END CASE;
RETURN;
END;
$$ LANGUAGE plpgsql;
This statement is transformated to:
three statement paths:
[0]
RAISE NOTICE 'other than 1,2,3,4';
[1]
RAISE NOTICE '1,2';
RAISE NOTICE '3';
[2]
RAISE NOTICE '4';
and case expression
CASE $1
WHEN 1 THEN 1
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 2
END;
When result is NULL then it uses 0 path.
Questions:
a) is possible to use SQL scanner? Now, scanner isn't directly used everywhere.
any notes and comments are welcome
Regards
Pavel Stehule
*** ./gram.y.orig 2008-03-28 17:33:45.0 +0100
--- ./gram.y 2008-03-31 13:46:08.0 +0200
***
*** 15,23
*/
#include plpgsql.h
-
#include parser/parser.h
!
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
--- 15,24
*/
#include plpgsql.h
#include parser/parser.h
! #include errno.h
! #include ctype.h
! #include string.h
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
***
*** 46,52
static char *check_label(const char *yytxt);
static void check_labels(const char *start_label,
const char *end_label);
!
%}
%name-prefix=plpgsql_yy
--- 47,54
static char *check_label(const char *yytxt);
static void check_labels(const char *start_label,
const char *end_label);
! static PLpgSQL_stmt *make_case(int lineno, PLpgSQL_expr *case_expr,
! List *when_clause_list, List *else_stmts);
%}
%name-prefix=plpgsql_yy
***
*** 79,84
--- 81,91
char *end_label;
List *stmts;
} loop_body;
+ struct
+ {
+ List *expr_list;
+ PLpgSQL_expr *expr;
+ } when_expr;
List *list;
PLpgSQL_type *dtype;
PLpgSQL_datum *scalar; /* a VAR, RECFIELD, or TRIGARG */
***
*** 95,100
--- 102,108
PLpgSQL_nsitem *nsitem;
PLpgSQL_diag_item *diagitem;
PLpgSQL_stmt_fetch *fetch;
+ PLpgSQL_when_clause *whenclause;
}
%type declhdr decl_sect
***
*** 109,115
%type str decl_stmts decl_stmt
%type expr expr_until_semi expr_until_rightbracket
! %type expr expr_until_then expr_until_loop
%type expr opt_exitcond
%type ival assign_var
--- 117,123
%type str decl_stmts decl_stmt
%type expr expr_until_semi expr_until_rightbracket
! %type expr expr_until_then expr_until_loop opt_expr_until_when
%type expr opt_exitcond
%type ival assign_var
***
*** 128,133
--- 136,145
%type stmt stmt_return stmt_raise stmt_execsql stmt_execsql_insert
%type stmt stmt_dynexecute stmt_for stmt_perform stmt_getdiag
%type stmt stmt_open stmt_fetch stmt_move stmt_close stmt_null
+ %type stmt stmt_case
+ %type when_expr case_when_expr
+ %type whenclause when_clause
+ %type list when_clause_list opt_case_default
%type list proc_exceptions
%type exception_block exception_sect
***
*** 154,159
--- 166,172
%token K_ASSIGN
%token K_BEGIN
%token K_BY
+ %token K_CASE
%token K_CLOSE
%token K_CONSTANT
%token K_CONTINUE
***
*** 611,616
--- 624,631
{ $$ = $1; }
| stmt_if
{ $$ = $1; }
+ | stmt_case
+ { $$ = $1; }
| stmt_loop
{ $$ = $1; }
| stmt_while
***
*** 809,814
--- 824,869
}
;
+ stmt_case : K_CASE lno opt_expr_until_when when_clause_list opt_case_default K_END K_CASE ';'
+ {
+ $$ = make_case($2, $3, $4, $5);
+ }
+ ;
+
+ opt_case_default :
+ {
+ $$ = NIL;
+ }
+ | K_ELSE proc_stmts
+ {
+ $$ = $2;
+ }
+ ;
+
+ when_clause_list : when_clause_list when_clause
+ {
+ $$ = lappend($1, $2);
+ }
+ | when_clause
+ {
+ $$ = list_make1($1);
+ }
+ ;
+
+ when_clause : K_WHEN lno case_when_expr proc_stmts
+ {
+ PLpgSQL_when_clause *new = palloc0(sizeof(PLpgSQL_when_clause));
+
+ new-cmd_type = PLPGSQL_STMT_WHEN_CLAUSE;
+ new-lineno = $2;
+ new-when_expr = $3.expr
Re: [PATCHES] Fwd: WIP: CASE statement for PL/pgSQL
Hello On 31/03/2008, Andrew Dunstan [EMAIL PROTECTED] wrote: Pavel Stehule wrote: correct queue Hello I finished this patch. Proposal: http://archives.postgresql.org/pgsql-hackers/2008-01/msg00696.php It's compatible with PL/SQL (Oracle) and SQL/PSM (ANSI). At the very least this patch is missing documentation and regression tests. yes, I know. Regress tests are not problem. This patch is only WIP and I'll to update this patch after commiting EXECUTE USING patch. But somebody maybe can comment this patch now, and I can save some time later. Pavel cheers andrew -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] actualised execute using patch
On 25/03/2008, Zdenek Kotala [EMAIL PROTECTED] wrote:
you have extra space onPavel Stehule napsal(a):
Hello
http://archives.postgresql.org/pgsql-patches/2007-10/msg00161.php
I actualized this patch for current CVS
Hi Pavel,
I tested your patch and it works regarding to proposal. However
I have some small comments:
1) you have extra space in ./src/pl/plpgsql/src/gram.y on line 1351
I didn't find it
2) there is some Czech text in regression tests diffs
fixed
3) I would like to add two more tests:
a) test which check if execute really takes 3rd parameter and inject it as a
first one:
create or replace function test(int,int,int) returns int as $$
declare
a int;
begin
execute 'select $1' into a using $3;
return a;
end
$$ language plpgsql;
select test(1,2,3);
b) and second test which control number of arguments:
for example:
create or replace function test(int,int,int) returns int as $$
begin
execute 'select $3' into a using $1;
return a;
end
$$ language plpgsql;
select test(1,2,3);
I did it
Zdenek
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-03-23 01:24:19.0 +0100
--- ./doc/src/sgml/plpgsql.sgml 2008-03-24 20:41:27.0 +0100
***
*** 1005,1011
commandEXECUTE/command statement is provided:
synopsis
! EXECUTE replaceable class=commandcommand-string/replaceable optional INTO optionalSTRICT/optional replaceabletarget/replaceable /optional;
/synopsis
where replaceablecommand-string/replaceable is an expression
--- 1005,1011
commandEXECUTE/command statement is provided:
synopsis
! EXECUTE replaceable class=commandcommand-string/replaceable optional INTO optionalSTRICT/optional replaceabletarget/replaceable /optional optional USING replaceable class=parameterexpression/replaceable optional, .../optional /optional;
/synopsis
where replaceablecommand-string/replaceable is an expression
***
*** 1046,1051
--- 1046,1066
If the literalSTRICT/ option is given, an error is reported
unless the query produces exactly one row.
/para
+
+ para
+ The commandEXECUTE/command statement can take parameters. To refer
+ to the parameters use $1, $2, $3, etc. Any parameter have to be bind to
+ any variable or any expression with USING clause. You cannot use bind
+ arguments to pass the names of schema objects to a dynamic SQL statement.
+ The use of arguments is perfect protection from SQL injection.
+ programlisting
+ EXECUTE 'SELECT count(*) FROM '
+ || tabname::regclass
+ || ' WHERE inserted_by = $1 AND inserted = $2'
+INTO c
+USING checked_user, checked_date;
+ /programlisting
+ /para
para
commandSELECT INTO/command is not currently supported within
***
*** 1997,2003
rows:
synopsis
optional lt;lt;replaceablelabel/replaceablegt;gt; /optional
! FOR replaceabletarget/replaceable IN EXECUTE replaceabletext_expression/replaceable LOOP
replaceablestatements/replaceable
END LOOP optional replaceablelabel/replaceable /optional;
/synopsis
--- 2012,2018
rows:
synopsis
optional lt;lt;replaceablelabel/replaceablegt;gt; /optional
! FOR replaceabletarget/replaceable IN EXECUTE replaceabletext_expression/replaceable optional USING replaceable class=parameterexpression/replaceable optional, .../optional /optional LOOP
replaceablestatements/replaceable
END LOOP optional replaceablelabel/replaceable /optional;
/synopsis
*** ./src/pl/plpgsql/src/gram.y.orig 2008-03-26 07:30:27.0 +0100
--- ./src/pl/plpgsql/src/gram.y 2008-03-24 20:41:27.0 +0100
***
*** 21,26
--- 21,27
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
+ int until3,
const char *expected,
const char *sqlstart,
bool isexpression,
***
*** 200,205
--- 201,207
%token K_THEN
%token K_TO
%token K_TYPE
+ %token K_USING
%token K_WARNING
%token K_WHEN
%token K_WHILE
***
*** 892,899
{
PLpgSQL_stmt_dynfors *new;
PLpgSQL_expr *expr;
! expr = plpgsql_read_expression(K_LOOP, LOOP);
new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
new-cmd_type = PLPGSQL_STMT_DYNFORS;
--- 894,907
{
PLpgSQL_stmt_dynfors *new;
PLpgSQL_expr *expr;
+ int term;
! expr = read_sql_construct(K_LOOP,
! K_USING,
! 0,
! LOOP|USING,
! SELECT ,
! true, true, term);
new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
new-cmd_type = PLPGSQL_STMT_DYNFORS;
***
*** 920,925
--- 928,948
yyerror(loop variable of loop over rows must be a record or row variable or list of scalar variables);
[PATCHES] generate_subscripts
Hello
This patch contains generate_subscripts functions, that generate
series of array's subscripts of some dimension:
postgres=#
create or replace function unnest2(anyarray)
returns setof anyelement as $$
select $1[i][j]
from generate_subscripts($1,1) g1(i),
generate_subscripts($1,2) g2(j);
$$ language sql immutable;
postgres=# select * from unnest2(array[[1,2],[3,4]]);
unnest2
-
1
2
3
4
(4 rows)
Proposal: http://archives.postgresql.org/pgsql-hackers/2007-10/msg00874.php
Regards
Pavel Stehule
*** ./doc/src/sgml/func.sgml.orig 2008-03-24 18:01:54.0 +0100
--- ./doc/src/sgml/func.sgml 2008-03-24 19:03:43.0 +0100
***
*** 10562,10569
para
This section describes functions that possibly return more than one row.
-Currently the only functions in this class are series generating functions,
-as detailed in xref linkend=functions-srf-series.
/para
table id=functions-srf-series
--- 10562,10567
***
*** 10641,10646
--- 10639,10697
(3 rows)
/programlisting
/para
+
+ table id=functions-srf-subscripts
+titleSubscripts Generating Functions/title
+tgroup cols=3
+ thead
+ row
+ entryFunction/entry
+ entryReturn Type/entry
+ entryDescription/entry
+ /row
+ /thead
+
+ tbody
+ row
+ entryliteralfunctiongenerate_subscripts/function(parameterarray annyarray/parameter, parameterdim int/parameter)/literal/entry
+ entrytypesetof int/type/entry
+ entry
+Generate a series of array's subscripts.
+ /entry
+ /row
+
+ row
+ entryliteralfunctiongenerate_subscripts/function(parameterarray annyarray/parameter, parameterdim int/parameter, parameterreverse boolean/parameter)/literal/entry
+ entrytypesetof int/type/entry
+ entry
+Generate a series of array's subscripts. When parameterreverse/parameter is true, then series is reverse order.
+ /entry
+ /row
+
+ /tbody
+/tgroup
+ /table
+
+ programlisting
+ -- unnest 2D array
+ create or replace function unnest2(anyarray)
+ returns setof anyelement as $$
+ select $1[i][j]
+from generate_subscripts($1,1) g1(i),
+ generate_subscripts($1,2) g2(j);
+ $$ language sql immutable;
+ CREATE FUNCTION
+ postgres=# select * from unnest2(array[[1,2],[3,4]]);
+ unnest2
+ -
+1
+2
+3
+4
+ (4 rows)
+ /programlisting
+
+
/sect1
sect1 id=functions-info
*** ./src/backend/utils/adt/arrayfuncs.c.orig 2008-03-24 17:38:45.0 +0100
--- ./src/backend/utils/adt/arrayfuncs.c 2008-03-24 18:12:35.0 +0100
***
*** 16,21
--- 16,22
#include ctype.h
+ #include funcapi.h
#include access/tupmacs.h
#include libpq/pqformat.h
#include parser/parse_coerce.h
***
*** 96,101
--- 97,108
int typlen, bool typbyval, char typalign);
static int array_cmp(FunctionCallInfo fcinfo);
+ typedef struct generate_subscripts_fctx
+ {
+ int4lower;
+ int4upper;
+ boolreverse;
+ } generate_subscripts_fctx;
/*
* array_in :
***
*** 4237,4239
--- 4244,4315
PG_RETURN_ARRAYTYPE_P(result);
}
+
+ /*
+ * array_subscripts(array anyarray, dim int, reverse bool)
+ * returns all subscripts of array for any dimension
+ */
+ Datum
+ array_subscripts_direction(PG_FUNCTION_ARGS)
+ {
+ return array_subscripts(fcinfo);
+ }
+
+ Datum
+ array_subscripts(PG_FUNCTION_ARGS)
+ {
+ FuncCallContext *funcctx;
+ MemoryContext oldcontext;
+ generate_subscripts_fctx *fctx;
+
+ /* stuff done only on the first call of the function */
+ if (SRF_IS_FIRSTCALL())
+ {
+ ArrayType *v = PG_GETARG_ARRAYTYPE_P(0);
+ int reqdim = PG_GETARG_INT32(1);
+ int *lb, *dimv;
+
+ /* create a function context for cross-call persistence */
+ funcctx = SRF_FIRSTCALL_INIT();
+
+ /* Sanity check: does it look like an array at all? */
+ if (ARR_NDIM(v) = 0 || ARR_NDIM(v) MAXDIM)
+ SRF_RETURN_DONE(funcctx);
+
+ /* Sanity check: was the requested dim valid */
+ if (reqdim = 0 || reqdim ARR_NDIM(v))
+ SRF_RETURN_DONE(funcctx);
+
+ /*
+ * switch to memory context appropriate for multiple function calls
+ */
+ oldcontext = MemoryContextSwitchTo(funcctx-multi_call_memory_ctx);
+ fctx = (generate_subscripts_fctx *) palloc(sizeof(generate_subscripts_fctx));
+
+ lb = ARR_LBOUND(v);
+ dimv = ARR_DIMS(v);
+
+ fctx-lower = lb[reqdim - 1];
+ fctx-upper = dimv[reqdim - 1] + lb[reqdim - 1] - 1
[PATCHES] actualised execute using patch
Hello
http://archives.postgresql.org/pgsql-patches/2007-10/msg00161.php
I actualized this patch for current CVS
Regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2008-03-23 01:24:19.0 +0100
--- ./doc/src/sgml/plpgsql.sgml 2008-03-24 20:41:27.0 +0100
***
*** 1005,1011
commandEXECUTE/command statement is provided:
synopsis
! EXECUTE replaceable class=commandcommand-string/replaceable optional INTO optionalSTRICT/optional replaceabletarget/replaceable /optional;
/synopsis
where replaceablecommand-string/replaceable is an expression
--- 1005,1011
commandEXECUTE/command statement is provided:
synopsis
! EXECUTE replaceable class=commandcommand-string/replaceable optional INTO optionalSTRICT/optional replaceabletarget/replaceable /optional optional USING replaceable class=parameterexpression/replaceable optional, .../optional /optional;
/synopsis
where replaceablecommand-string/replaceable is an expression
***
*** 1046,1051
--- 1046,1066
If the literalSTRICT/ option is given, an error is reported
unless the query produces exactly one row.
/para
+
+ para
+ The commandEXECUTE/command statement can take parameters. To refer
+ to the parameters use $1, $2, $3, etc. Any parameter have to be bind to
+ any variable or any expression with USING clause. You cannot use bind
+ arguments to pass the names of schema objects to a dynamic SQL statement.
+ The use of arguments is perfect protection from SQL injection.
+ programlisting
+ EXECUTE 'SELECT count(*) FROM '
+ || tabname::regclass
+ || ' WHERE inserted_by = $1 AND inserted = $2'
+INTO c
+USING checked_user, checked_date;
+ /programlisting
+ /para
para
commandSELECT INTO/command is not currently supported within
***
*** 1997,2003
rows:
synopsis
optional lt;lt;replaceablelabel/replaceablegt;gt; /optional
! FOR replaceabletarget/replaceable IN EXECUTE replaceabletext_expression/replaceable LOOP
replaceablestatements/replaceable
END LOOP optional replaceablelabel/replaceable /optional;
/synopsis
--- 2012,2018
rows:
synopsis
optional lt;lt;replaceablelabel/replaceablegt;gt; /optional
! FOR replaceabletarget/replaceable IN EXECUTE replaceabletext_expression/replaceable optional USING replaceable class=parameterexpression/replaceable optional, .../optional /optional LOOP
replaceablestatements/replaceable
END LOOP optional replaceablelabel/replaceable /optional;
/synopsis
*** ./src/pl/plpgsql/src/gram.y.orig 2008-01-01 20:46:00.0 +0100
--- ./src/pl/plpgsql/src/gram.y 2008-03-24 20:41:27.0 +0100
***
*** 21,26
--- 21,27
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
+ int until3,
const char *expected,
const char *sqlstart,
bool isexpression,
***
*** 200,205
--- 201,207
%token K_THEN
%token K_TO
%token K_TYPE
+ %token K_USING
%token K_WARNING
%token K_WHEN
%token K_WHILE
***
*** 892,899
{
PLpgSQL_stmt_dynfors *new;
PLpgSQL_expr *expr;
! expr = plpgsql_read_expression(K_LOOP, LOOP);
new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
new-cmd_type = PLPGSQL_STMT_DYNFORS;
--- 894,907
{
PLpgSQL_stmt_dynfors *new;
PLpgSQL_expr *expr;
+ int term;
! expr = read_sql_construct(K_LOOP,
! K_USING,
! 0,
! LOOP|USING,
! SELECT ,
! true, true, term);
new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
new-cmd_type = PLPGSQL_STMT_DYNFORS;
***
*** 920,925
--- 928,948
yyerror(loop variable of loop over rows must be a record or row variable or list of scalar variables);
}
new-query = expr;
+
+ if (term == K_USING)
+ {
+ for(;;)
+ {
+ int term;
+
+ expr = read_sql_construct(',', K_LOOP, 0, , or LOOP,
+ SELECT ,
+ true, true, term);
+ new-params = lappend(new-params, expr);
+ if (term == K_LOOP)
+ break;
+ }
+ }
$$ = (PLpgSQL_stmt *) new;
}
***
*** 954,959
--- 977,983
*/
expr1 = read_sql_construct(K_DOTDOT,
K_LOOP,
+ 0,
LOOP,
SELECT ,
true,
***
*** 975,980
--- 999,1005
/* Read and check the second one */
expr2 = read_sql_construct(K_LOOP,
K_BY,
+ 0,
LOOP,
SELECT ,
true,
***
*** 1222,1228
[PATCHES] actualized SQL/PSM patch
Hello I actualized sql/psm patch. This patch can be downloaded from http://www.pgsql.cz/patches/plpgpsm.diff.gz Documentation is on wiki http://www.pgsql.cz/index.php/SQL/PSM_Manual Regards Pavel Stehule -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgextra=pgsql-patches
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 29/01/2008, Peter Eisentraut [EMAIL PROTECTED] wrote: Am Montag, 28. Januar 2008 schrieb Pavel Stehule: this patch define new function flag - OBFUSCATE. With this flag encrypted source code is stored to probin column. Password is stored in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server does (where privileged users can access system tables with source code or can use debugger). Have you thought about a solution that applies the regular access privileges to pg_proc in order to hide some content from less privileged users? it's second way, and maybe better. It can close way to table definitions too (and this request is adequate too). But you cannot to hide complete column, visibility depend on content and it can be slow, complex :(. Encrypt, decrypt aren't fast too. Pavel -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Dave Page [EMAIL PROTECTED] wrote: On Jan 28, 2008 2:26 PM, Pavel Stehule [EMAIL PROTECTED] wrote: sure, but do you know, Tom dislikes new columns in pg_proc :). Tom doesn't seem to like the idea of obfuscation of function code much either :-) This patch is usable sample of one possible solution and doesn't need initdb. And there is dependency on pgcrypto :(. But it is simply and it does all what is expected. Some customers wonted it. But I am not sure if similar patch can be accepted - this is prototype. And when I'll have some signals so patch can be commited I'll send final version with obfuscate col in pg_proc. Any patch of pg_proc needs two hours of work, and any change needs actualization - so lot of maybe useless work. Yeah, I realise tweaking pg_proc is a large job, and wouldn't expect you to necessarily do it immediately - I just wanted to throw my requirements from a tools perspective into the inevitable discussion. with obfuscate col in pg_proc source can be little bit more readable and robust - current patch is +/- fast hack - so your requirement is accurate. Regards Pavel . Cheers, Dave. ---(end of broadcast)--- TIP 1: 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: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Gregory Stark [EMAIL PROTECTED] wrote: Someone along the way suggested doing this as a kind of wrapper PL language. So you would have a PL language like obfuscate:plperl which would obfuscate the source code on the way in. Then when you execute a function it would deobfuscate the source code and then just pass it to the normal plperl. you can call Deobfuscate proc from any language handler - no problem In such a scheme I think you would put the key in an attribute of the language. Either in pg_lang or some configuration location which the obfuscate:plperl interpreter knows where to find. what is advantage? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Dave Page [EMAIL PROTECTED] wrote: On Jan 28, 2008 12:51 PM, Pavel Stehule [EMAIL PROTECTED] wrote: Hello this patch define new function flag - OBFUSCATE. With this flag encrypted source code is stored to probin column. Password is stored in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server does (where privileged users can access system tables with source code or can use debugger). ToDo: Dump Without making any comment of whether or not we should actually do this, a flag in pg_proc to indicate that the function is obfuscated would be handy for apps like pgAdmin, rather than assuming a - in prosrc has that meaning (which may be valid for some interpreters). sure, but do you know, Tom dislikes new columns in pg_proc :). This patch is usable sample of one possible solution and doesn't need initdb. And there is dependency on pgcrypto :(. But it is simply and it does all what is expected. Some customers wonted it. But I am not sure if similar patch can be accepted - this is prototype. And when I'll have some signals so patch can be commited I'll send final version with obfuscate col in pg_proc. Any patch of pg_proc needs two hours of work, and any change needs actualization - so lot of maybe useless work. Pavel /D ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Andrew Dunstan [EMAIL PROTECTED] wrote: Pavel Stehule wrote: Hello this patch define new function flag - OBFUSCATE. With this flag encrypted source code is stored to probin column. Password is stored in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server does (where privileged users can access system tables with source code or can use debugger) ToDo: Dump Maybe a better TODO would be to do this task in the way that has previously been suggested: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php I'm certainly not happy about any proposal to put a password/key in a GUC var - that strikes me as a major footgun. why? we cannot ensure bigger real security. Anybody with superuser rights can add modules that show source code of plpgsql procedure, or can run debugger and attach postgres process. p.s. this topic was discussed in http://markmail.org/message/r6jy7m6oryi5owyb Pavel cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Tom Lane [EMAIL PROTECTED] wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Maybe a better TODO would be to do this task in the way that has previously been suggested: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00258.php I'm certainly not happy about any proposal to put a password/key in a GUC var - that strikes me as a major footgun. We didn't really have a better solution to the key management problem, though, did we? At least I don't see anything about it in that thread. However, I definitely agree that a separate loadable PL is the way to go for functionality of this sort. There is no way that a dependency on pgcrypto is going to be accepted into core, not even in the (ahem) obfuscated way that it's presented here. Do you thing some binary module that load some encrypted sources from files? It can be possible too. But if source code will be stored in pg_proc, then we need third method. Some like obfuscate (prev. are validate and call), because we can't to store plain text to prosrc col. My patch is only solution for some users, and I know about problem with dependency. Reagards Pavel Stehule regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Gregory Stark [EMAIL PROTECTED] wrote: Pavel Stehule [EMAIL PROTECTED] writes: In such a scheme I think you would put the key in an attribute of the language. Either in pg_lang or some configuration location which the obfuscate:plperl interpreter knows where to find. what is advantage? It wouldn't require any core changes. It would be just another PL language to load which can be installed like other ones. This could be a big advantage because it doesn't look like there is a lot of support for putting th obfuscation directly into the core code. can be. but I am afraid so any changes are necessary in core too Pavel -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PATCHES] WIP: plpgsql source code obfuscation
Hello
this patch define new function flag - OBFUSCATE. With this flag
encrypted source code is stored to probin column. Password is stored
in GUC_SUPERUSER_ONLY item - it is similar security like SQL Server
does (where privileged users can access system tables with source code
or can use debugger).
ToDo: Dump
Sample:
postgres=# show obfuscator_password;
obfuscator_password
---
moje supertajne heslo
(1 row)
postgres=# \x
Expanded display is on.
postgres=# create or replace function fx() returns int as $$begin
return -1; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]---+---
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | begin return -1; end;
Description |
postgres=# ALTER FUNCTION fx() obfuscate;
NOTICE: begin return -1; end;
ALTER FUNCTION
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]---+-
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | -
Description |
postgres=# select fx();
-[ RECORD 1 ]
fx | -1
postgres=# create or replace function fx() returns int as $$begin
return -1; end; $$ language plpgsql obfuscate;
CREATE FUNCTION
postgres=# select fx();
-[ RECORD 1 ]
fx | -1
postgres=# \df+ fx
List of functions
-[ RECORD 1 ]---+-
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | -
Description |
postgres=# select * from pg_proc where proname = 'fx';
-[ RECORD 1
]--+
proname| fx
pronamespace | 2200
proowner | 16385
prolang| 16421
procost| 100
prorows| 0
proisagg | f
prosecdef | f
proisstrict| f
proretset | f
provolatile| v
pronargs | 0
prorettype | 23
proargtypes|
proallargtypes |
proargmodes|
proargnames|
prosrc | -
probin |
\231\003_\266\361\214}\231\240L/\020\232\036c\234\315P\236\266I\370\324\222
proconfig |
proacl |
[EMAIL PROTECTED] ~]$ psql -U bob postgres
Welcome to psql 8.3RC2, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres= \x
Expanded display is on.
postgres= show obfuscator_password;
ERROR: must be superuser to examine obfuscator_password
postgres= select fx();
-[ RECORD 1 ]
fx | -1
postgres= \df+ fx
List of functions
-[ RECORD 1 ]---+-
Schema | public
Name| fx
Result data type| integer
Argument data types |
Volatility | volatile
Owner | bob
Language| plpgsql
Source code | -
Description |
postgres= select * from pg_proc where proname = 'fx';
-[ RECORD 1
]--+
proname| fx
pronamespace | 2200
proowner | 16385
prolang| 16421
procost| 100
prorows| 0
proisagg | f
prosecdef | f
proisstrict| f
proretset | f
provolatile| v
pronargs | 0
prorettype | 23
proargtypes|
proallargtypes |
proargmodes|
proargnames|
prosrc | -
probin |
\231\003_\266\361\214}\231\240L/\020\232\036c\234\315P\236\266I\370\324\222
proconfig |
proacl |
*** ./src/backend/catalog/pg_proc.c.orig 2008-01-27 21:29:42.0 +0100
--- ./src/backend/catalog/pg_proc.c 2008-01-28 11:13:31.0 +0100
***
*** 27,32
--- 27,33
#include mb/pg_wchar.h
#include miscadmin.h
#include parser/parse_type.h
+ #include parser/parse_func.h
#include tcop/pquery.h
#include tcop/tcopprot.h
#include utils/acl.h
***
*** 34,39
--- 35,41
#include utils/lsyscache.h
#include utils/syscache.h
+ extern char *obfuscator_password;
Datum fmgr_internal_validator(PG_FUNCTION_ARGS);
Datum fmgr_c_validator(PG_FUNCTION_ARGS);
***
*** 45,51
static bool match_prosrc_to_literal(const char *prosrc, const char *literal,
int cursorpos, int *newcursorpos);
-
/*
* ProcedureCreate
*
--- 47,52
***
*** 74,80
Datum parameterNames,
Datum proconfig,
float4 procost,
! float4 prorows)
{
Oid retval;
int parameterCount;
--- 75,82
Datum
Re: [PATCHES] WIP: plpgsql source code obfuscation
On 28/01/2008, Gregory Stark [EMAIL PROTECTED] wrote: Pavel Stehule [EMAIL PROTECTED] writes: Do you thing some binary module that load some encrypted sources from files? It can be possible too. But if source code will be stored in pg_proc, then we need third method. Some like obfuscate (prev. are validate and call), because we can't to store plain text to prosrc col. Is there a reason you couldn't, for instance, provide a function which takes source code and encrypts it. Then you would write dump the data it spits into your function declaration like: CREATE FUNCTION foo() returns integer AS $$ ... base64 encoded data $$ language obfuscated:plperl; it's solve problem with dump well, but it's similar to my solution. obfuscated:plperl can be virtual language - we can have one common handler, because there is same work. I am not sure. This doesn't care any better security, only add some other necessary external toolkit. With obfuscate column or obfuscate language (it carry same information) I can use prosrc and I have not problem with dump too. It is true, so obfuscate languages move dependency to out of core - but it is more complex. obfuscated:plperl's handler function would just decrypt it and pass it off to plperl. you need same handler for plpgsql, python, sql, ... so why don't do it generally? Pavel There is a validator function which gets called when you create a function but I don't think it has any opportunity to substitute its result for the original in prosrc. That might be interesting for other applications like compiled languages, though I think they would still want to save the source in prosrc and the bytecode in probin. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [8.4] Updated WITH clause patch (non-recursive)
Hello On 27/01/2008, Neil Conway [EMAIL PROTECTED] wrote: On Sun, 2008-01-27 at 09:17 +, Gregory Stark wrote: Tom's feeling at the time was that even though it was providing something from the standard, it wasn't actually allowing the user to do anything he couldn't before. I think this feature has value: +1 I thing so is better commit smaller pieces more often than one time one big patch. Nine months long feature freeze time is enough. Regards Pavel Stehule (1) This is SQL-standard syntax (and not even wacko syntax, at that!), and there is merit in implementing it on those grounds alone. (2) It is supported by DB2, MS SQL and Oracle, so there is a further compatibility argument to be made. (3) It avoids the need to repeat subqueries multiple times in the main query, which can make queries more concise. Defining subqueries outside the main SELECT body can also have readability advantages. If it doesn't provide any additional expressive capabilities then I think he didn't like taking with as a reserved word. Note that we can make WITH a type_func_name_keyword, rather than a full-on reserved_keyword, which reduces the force of this argument slightly. If we're going to implement recursive queries eventually (which we almost surely will, whether in 8.4 or a future release), we'll need to make WITH more reserved at some point anyway, so I don't see much to be gained in the long run by delaying it. -Neil ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] WIP: variadic function, named params
Hello,
there is one implementation of variadic functions. The base premis is
transformation type:
anyparams - n x any
anyelements - n x anyelement
Currently variadic functions can be only in C language. PL/pgSQL
cannot access to array of params. I implented some JSON functions
(based on Bauman's libraries for MySQL). With this extension we are
able to write some sophistic libraries.
What do you thing about this concept? Has any sense continue in this project?
Regards
Pavel Stehule
postgres=# select json_members('aaa',1,'',20);
json_members
---
aaa:1,:20
(1 row)
postgres=# select json_members('aaa',1);
json_members
--
aaa:1
(1 row)
postgres=# select * from fog;
a | b | c
---+--+
| ahoj |
| ahoj | 2008-01-26
1 | | 2008-01-26
(3 rows)
postgres=# select json_object(a,b,c as cc) from fog;
json_object
--
{a:NaN,b:ahoj,cc:null}
{a:NaN,b:ahoj,cc:2008-01-26}
{a:1,b:null,cc:2008-01-26}
(3 rows)
postgres=# select json_object('Pavel' as name, 'Stehule' as surname);
json_object
--
{name:Pavel,surname:Stehule}
(1 row)
postgres=# select json_array(a,b,c) from fog;
json_array
---
[NaN,ahoj,null]
[NaN,ahoj,2008-01-26]
[1,null,2008-01-26]
(3 rows)
CREATE OR REPLACE FUNCTION json_array(anyparams) RETURNS json
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE;
CREATE OR REPLACE FUNCTION json_object(anyparams) RETURNS json
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE;
CREATE OR REPLACE FUNCTION json_members(anyparams) RETURNS json
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE;
variadic_functions.diff.gz
Description: GNU Zip compressed data
json.tgz
Description: GNU Zip compressed data
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] DDL in EDB-SPL
Wrong address :) Pavel On 12/12/2007, Heikki Linnakangas [EMAIL PROTECTED] wrote: While looking at the package function precedence problem, I bumped into another unrelated bug: According to a quick Google search, Oracle doesn't accept DDL in PL/SQL; you have to use EXECUTE IMMEDIATE to do that. Trying to run DDL in the edb-spl fails with a bizarre error message. For example, for CREATE TABLE footable (full test case attached): ERROR: syntax error at or near footable LINE 1: CREATE footable2 (id integer) So the TABLE token seems to be stripped away somewhere. This begs the question of what happens with CREATE TEMPORARY TABLE. Lo and behold, it does what you might've guessed, kind of. TEMPORARY is stripped away, leaving just CREATE TABLE tablename. However, we've set the package namespace as the special namespace, and that's the current default creation namespace. Therefore the table gets created inside the package namespace. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PATCHES] result of convert_to is bytea
Hello documentation fix. result of convert_to is bytea, not text. Regards Pavel Stehule *** ./src/sgml/func.sgml.orig 2007-12-12 17:18:55.0 +0100 --- ./src/sgml/func.sgml 2007-12-12 17:19:56.0 +0100 *** *** 1386,1392 literalfunctionconvert_to/function(parameterstring/parameter typetext/type, parameterdest_encoding/parameter typename/type)/literal /entry !entrytypetext/type/entry entry Convert string to parameterdest_encoding/parameter. /entry --- 1386,1392 literalfunctionconvert_to/function(parameterstring/parameter typetext/type, parameterdest_encoding/parameter typename/type)/literal /entry !entrytypebytea/type/entry entry Convert string to parameterdest_encoding/parameter. /entry ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PATCHES] plpgsql, forc statement
Hello per proposal http://archives.postgresql.org/pgsql-hackers/2007-11/msg01012.php This patch allows iteration over cursor, so longer SELECTs can be written outside cycle. Clean some related redundant code too. Regards Pavel Stehule forc.diff.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] quote_literal(anyelement) was: quote_literal(integer) does not exist
Hello
this patch add anyelement variants of functions quote_literal and
quote_ident. Changes of casting rules in 8.3 has impact on these
functions, but there is same situation like operator ||. Is clear so
arguments is string or have to cast to string.
Regards
Pavel Stehule
*** ./src/include/catalog/pg_proc.h.orig 2007-11-25 17:33:44.0 +0100
--- ./src/include/catalog/pg_proc.h 2007-11-25 17:44:17.0 +0100
***
*** 2631,2636
--- 2631,2641
DESCR(quote an identifier for usage in a querystring);
DATA(insert OID = 1283 ( quote_literalPGNSP PGUID 12 1 0 f f t f i 1 25 25 _null_ _null_ _null_ quote_literal - _null_ _null_ ));
DESCR(quote a literal for usage in a querystring);
+ DATA(insert OID = 1191 ( quote_ident PGNSP PGUID 14 1 0 f f t f i 1 25 2283 _null_ _null_ _null_ select quote_ident($1::text) - _null_ _null_ ));
+ DESCR(quote an identifier for usage in a querystring);
+ DATA(insert OID = 1192 ( quote_literalPGNSP PGUID 14 1 0 f f t f i 1 25 2283 _null_ _null_ _null_ select quote_literal($1::text) - _null_ _null_ ));
+ DESCR(quote a literal for usage in a querystring);
+
DATA(insert OID = 1798 ( oidin PGNSP PGUID 12 1 0 f f t f i 1 26 2275 _null_ _null_ _null_ oidin - _null_ _null_ ));
DESCR(I/O);
*** ./src/test/regress/expected/strings.out.orig 2007-11-25 18:01:26.0 +0100
--- ./src/test/regress/expected/strings.out 2007-11-25 17:57:58.0 +0100
***
*** 1240,1242
--- 1240,1269
a\bcd | a\b'cd | a\b''cd | abcd\ | ab\'cd | \\
(1 row)
+ --
+ -- test quote_literal and quote_ident functions
+ --
+ select quote_literal('message');
+ quote_literal
+ ---
+ 'message'
+ (1 row)
+
+ select quote_literal(10);
+ quote_literal
+ ---
+ '10'
+ (1 row)
+
+ select quote_ident('message');
+ quote_ident
+ -
+ message
+ (1 row)
+
+ select quote_ident(10);
+ quote_ident
+ -
+ 10
+ (1 row)
+
*** ./src/test/regress/sql/strings.sql.orig 2007-11-25 17:56:45.0 +0100
--- ./src/test/regress/sql/strings.sql 2007-11-25 18:05:32.0 +0100
***
*** 450,452
--- 450,460
set standard_conforming_strings = off;
select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\' as f4, 'ab\\\'cd' as f5, '' as f6;
+
+ --
+ -- test quote_literal and quote_ident functions
+ --
+ select quote_literal('message');
+ select quote_literal(10);
+ select quote_ident('message');
+ select quote_ident(10);
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] tsearch2api .. wrapper for integrated fultext
On 13/11/2007, Tom Lane [EMAIL PROTECTED] wrote: I wrote: I've modified Pavel's version into something that seems to support this approach --- at least I can load the 8.2 tsearch regression test database into 8.3 after loading this. Still needs some polishing probably, and some more testing. Comments? I've committed this, replacing the old contrib/tsearch2 code. It successfully runs most of the old module's regression test, after some minor adjustments for default configuration names and suchlike. One large omission is that the rewrite(ARRAY[]) aggregate isn't there. AFAIR, we removed that just because it seemed a poorly designed API, not because it didn't work. I'm thinking we should probably pull the code for it out of the CVS history and stick it into contrib/tsearch2. Any thoughts pro or con? +1 Pavel regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] tsearch2api .. wrapper for integrated fultext
Hello On 10/11/2007, Tom Lane [EMAIL PROTECTED] wrote: Pavel Stehule [EMAIL PROTECTED] writes: this is revised version A couple of thoughts here: * What is the point of creating stub functions for things that users won't attempt to call directly, such as opclass support functions and the old dictionary support functions? Couldn't we just leave those out and save some code? we can. I don't understand to tsearch2 well, so this wrapper is complete. There are not necessary all unsupported functions. But these improvisation can be done simply in C preprocessor. * The WRAPPER_FUNCTION stuff seems unnecessarily inefficient --- can't we just declare those as LANGUAGE INTERNAL and link the SQL definition directly to the built-in function? it's little bit inefficient, but it's more consistent and readable. So it's reason. * The SQL file doesn't create any of the old types (public.tsvector etc) so it seems still a long ways short of ensuring that an old dump file can be reloaded. Maybe I don't understand exactly how you intend it to interact with the definitions that will be in the dump file. with this version of wrapper you cannot load old dumps. It allows application compatibility. Dump was readable with older variant which is really ugly and that is on pgfoundry. Steps with this wrapper: a) uninstall tsearch2 b) dump c) install 8.3 and configure fulltext d) load e) load wrapper Pavel Stehule regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] tsearch2api .. wrapper for integrated fultext
On 10/11/2007, Tom Lane [EMAIL PROTECTED] wrote: Pavel Stehule [EMAIL PROTECTED] writes: On 10/11/2007, Tom Lane [EMAIL PROTECTED] wrote: * The SQL file doesn't create any of the old types (public.tsvector etc) so it seems still a long ways short of ensuring that an old dump file can be reloaded. Maybe I don't understand exactly how you intend it to interact with the definitions that will be in the dump file. with this version of wrapper you cannot load old dumps. It allows application compatibility. Dump was readable with older variant which is really ugly and that is on pgfoundry. Steps with this wrapper: a) uninstall tsearch2 b) dump c) install 8.3 and configure fulltext d) load e) load wrapper That seems like a non-starter. Existing tsearch2 installations will have tsvector columns in their tables, so I don't see how they are going to uninstall tsearch2 in the existing database. The other problem is that I think we have to provide a migration path for people who already have dump files (and, maybe, no longer have the original installation). For people who have custom or tar-format dumps, the previously posted script to strip out the old tsearch2 objects during pg_restore would help ... but it's useless if you used text dump (notably including pg_dumpall output). Another problem that was already noted was that the dump might contain explicit references to public.tsvector, or some other schema that you put the tsearch2 objects in. I forgot. With perl script, dump can be transformed to readable form. I see forms of port TSearch to integrated full text: 1. clean and prefered .. dump and application are modified 2. dump is modified, application works via some api 3. full wrapper with known issues 4. special smart mode where TSearch2 API functions are dynamicly converted to fulltext api like get function name if it some from tsearch2 api transform it or forgot it else create function but perl script do it well and outside The approach that I was hoping to see was a) dump b) install 8.3 and configure fulltext c) load wrapper (into same schema as you used for tsearch2 before) d) load dump file It was my original goal. But there is lot of issues. I found different problem, that have to be solved if wrapper have to be dump compatible. there is about five functions with same name, and I have problem with wrapping, because I create recursive calling. I am not able select integrated functions. That was reason for use prefix tsa. Since pg_dump doesn't do CREATE OR REPLACE, step (d) would result in a lot of error messages, but it wouldn't overwrite any of the function definitions installed by the wrapper. We could possibly deal with the schema issue by having the wrapper create public.tsvector as a domain for pg_catalog.tsvector, etc. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] tsearch2api .. wrapper for integrated fultext
On 10/11/2007, Tom Lane [EMAIL PROTECTED] wrote: Pavel Stehule [EMAIL PROTECTED] writes: I found different problem, that have to be solved if wrapper have to be dump compatible. there is about five functions with same name, and I have problem with wrapping, because I create recursive calling. I am not able select integrated functions. That was reason for use prefix tsa. I don't follow. The functions all have distinct names at the C-code level, so what's the problem? they don't have distinct names tsearch_length rewrite_query ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] tsearch2api .. wrapper for integrated fultext
wait two days, please. Pavel I'll send finished patch on Sunday. On 09/11/2007, Bruce Momjian [EMAIL PROTECTED] wrote: Should I apply this or wait for a final version? --- Pavel Stehule wrote: Hello I am sending light wrapper of integrated fulltext. API is compatible with tsearch2. Regards Pavel Stehule ToDo: rewrite functions [ Attachment, skipping... ] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] tsearch2api .. wrapper for integrated fultext
On 09/11/2007, Tom Lane [EMAIL PROTECTED] wrote: Magnus Hagander [EMAIL PROTECTED] writes: A thought on this - should it not go in contrib/tsearch2 replacing the old deprecated stuff, instead of creating yet aother contrib dir? It can be moved - no problem. That was the idea, I thought. This proposed patch is in need of review, which I'd been hoping to get to tomorrow; but since Pavel says he's about to send in a revised version, I will wait for that ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] tsearch2api .. wrapper for integrated fultext
Hello I am sending light wrapper of integrated fulltext. API is compatible with tsearch2. Regards Pavel Stehule ToDo: rewrite functions diff -c -N -r contrib.old/tsearch2api/Makefile contrib/tsearch2api/Makefile *** contrib.old/tsearch2api/Makefile 1970-01-01 01:00:00.0 +0100 --- contrib/tsearch2api/Makefile 2007-11-07 08:02:56.0 +0100 *** *** 0 --- 1,17 + MODULE_big = tsearch2 + OBJS= tsearch2api.o + + DATA_built = tsearch2.sql + + ifdef USE_PGXS + PGXS = $(shell pg_config --pgxs) + include $(PGXS) + else + subdir = contrib/tsearch2api + top_builddir = ../.. + include $(top_builddir)/src/Makefile.global + include $(top_srcdir)/contrib/contrib-global.mk + endif + + tsearch2.sql: tsearch.sql.in + sed -e 's,MODULE_PATHNAME,$$libdir/$(MODULE_big),g' $ $@ diff -c -N -r contrib.old/tsearch2api/tsearch.sql.in contrib/tsearch2api/tsearch.sql.in *** contrib.old/tsearch2api/tsearch.sql.in 1970-01-01 01:00:00.0 +0100 --- contrib/tsearch2api/tsearch.sql.in 2007-11-07 08:05:25.0 +0100 *** *** 0 --- 1,739 + SET search_path = public, pg_catalog; + + BEGIN; + + --dict interface + CREATE FUNCTION lexize(oid, text) + RETURNS _text + as 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION lexize(text, text) + RETURNS _text + as 'MODULE_PATHNAME', 'lexize_byname' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION lexize(text) + RETURNS _text + as 'MODULE_PATHNAME', 'lexize_bycurrent' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION set_curdict(int) + RETURNS void + as 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION set_curdict(text) + RETURNS void + as 'MODULE_PATHNAME', 'set_curdict_byname' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + --built-in dictionaries + CREATE FUNCTION dex_init(internal) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C; + + CREATE FUNCTION dex_lexize(internal,internal,int4) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION snb_en_init(internal) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C; + + CREATE FUNCTION snb_lexize(internal,internal,int4) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION snb_ru_init_koi8(internal) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C; + + CREATE FUNCTION snb_ru_init_utf8(internal) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C; + + CREATE FUNCTION spell_init(internal) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C; + + CREATE FUNCTION spell_lexize(internal,internal,int4) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION syn_init(internal) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C; + + CREATE FUNCTION syn_lexize(internal,internal,int4) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION thesaurus_init(internal) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C; + + CREATE FUNCTION thesaurus_lexize(internal,internal,int4,internal) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + --sql-level interface + CREATE TYPE tokentype + as (tokid int4, alias text, descr text); + + CREATE FUNCTION token_type(int4) + RETURNS setof tokentype + as 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION token_type(text) + RETURNS setof tokentype + as 'MODULE_PATHNAME', 'token_type_byname' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION token_type() + RETURNS setof tokentype + as 'MODULE_PATHNAME', 'token_type_current' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION set_curprs(int) + RETURNS void + as 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION set_curprs(text) + RETURNS void + as 'MODULE_PATHNAME', 'set_curprs_byname' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE TYPE tokenout + as (tokid int4, token text); + + CREATE FUNCTION parse(oid,text) + RETURNS setof tokenout + as 'MODULE_PATHNAME' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION parse(text,text) + RETURNS setof tokenout + as 'MODULE_PATHNAME', 'parse_byname' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + CREATE FUNCTION parse(text) + RETURNS setof tokenout + as 'MODULE_PATHNAME', 'parse_current' + LANGUAGE C + RETURNS NULL ON NULL INPUT; + + --default parser + CREATE FUNCTION prsd_start(internal,int4) + RETURNS internal + as 'MODULE_PATHNAME' + LANGUAGE C; + + CREATE FUNCTION prsd_getlexeme(internal,internal,internal) + RETURNS int4 + as 'MODULE_PATHNAME' + LANGUAGE C; + + CREATE FUNCTION prsd_end(internal) + RETURNS void
Re: [PATCHES] V0.2 patch for TODO Item: SQL-language reference parameters by name.
Any thoughts? I think a prefix of ':' would be good, as it's already a standard, kinda. Anybody who names a database object :foo deserves whatever happens to them :P Cheers, David. +1 ':' is shorter than 'this'. And ':' is well known in SQL area. Pavel ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
Hello all, Hereby an alpha version regarding the: TODO Item: SQL-language reference parameters by name. what about name's collision? Maybe is better use some prefix, like $ or :. Without it we only propagate one problem from plpgsql to others languages. It can be more wide used: * named params in prepared statements * named params in SPI * .. Regards Pavel Stehule ---(end of broadcast)--- TIP 1: 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: [PATCHES] V0.1 patch for TODO Item: SQL-language reference parameters by name.
On 02/11/2007, Gevik Babakhani [EMAIL PROTECTED] wrote: Hi, what about name's collision? Maybe is better use some prefix, like $ or :. Without it we only propagate one problem from plpgsql to others languages. Please explain. Perhaps I am wrong, but plpgsql handles arsgument names before it passes the query to be executed. Please see: plpgsql/pl_comp.c/do_compile(...)/line: 393 Regards, Gevik. it's one from mystic bugs: create table t(a integer, b integer); insert into t values(10,20); create function foo(a integer) returns integer as $$ select a from t where a b and a = 10; $$ languge sql; select foo(20); output? expected 10, but you will get NULL! Regards Pavel Stehule so some prefixes can help create function foo(a integer) returns integer as $$ select a from t where :a b and a = 10; $$ languge sql; Oracle use symbol ':' I don't know what others databases has. Regards Pavel Stehule ---(end of broadcast)--- TIP 1: 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
[PATCHES] actualised czech faq
Hello I am sendin actualised czech FAQ. It was updated, encoded to utf8 and checked by spellckecker Regards Pavel Stehule actualised_czech_faq.diff.tar.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 1: 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: [PATCHES] EXECUTE USING for plpgsql (for 8.4)
This doesn't work:
create function exc_using(varchar) returns varchar
as $$
declare v varchar;
begin
execute 'select upper($1)' into v using ('aa');
it cannot work. Your parameter is row. But
into v using 'aaa' doesn't work too :(
ERROR: failed to find conversion function from unknown to text
CONTEXT: SQL statement select upper($1)
you have to specify type: use argument, variable or casting
using text 'aaa'; or select upper($1::text)
It is question for Tom. Why prepared statement cannot cast from literal to text
http://www.nabble.com/Blowback-from-text-conversion-changes-t3977711.html
I also noted that the patch makes USING a keyword. Not sure if we care
about that or not.
I am afraid to change well know syntax (SQL/PSM use it in same context too).
Pavel
---(end of broadcast)---
TIP 1: 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: [PATCHES] EXECUTE USING for plpgsql (for 8.4)
2007/10/23, Heikki Linnakangas [EMAIL PROTECTED]:
Pavel Stehule wrote:
This doesn't work:
create function exc_using(varchar) returns varchar
as $$
declare v varchar;
begin
execute 'select upper($1)' into v using ('aa');
it cannot work. Your parameter is row.
Really? execute 'select upper($1)' into v using ('aa'::varchar);
works, as does execute 'select $1 + 1' into v using (12345);.
No, propably not. I am not sure, when Postgres grouping fields into
row. Problem is only in unknown literal.
But into v using 'aaa' doesn't work too :(
ERROR: failed to find conversion function from unknown to text
CONTEXT: SQL statement select upper($1)
you have to specify type: use argument, variable or casting
using text 'aaa'; or select upper($1::text)
It is question for Tom. Why prepared statement cannot cast from literal to
text
http://www.nabble.com/Blowback-from-text-conversion-changes-t3977711.html
Yeah, I suppose we'll just live with that. Using literals as arguments
is kind of pointless anyway, since you could as well put the literal in
the query as well and not bother with the USING.
I also noted that the patch makes USING a keyword. Not sure if we care
about that or not.
I am afraid to change well know syntax (SQL/PSM use it in same context too).
No I think the syntax is fine. I'm just wondering if it really has to be
a reserved keyword to implement that syntax. Looking at the plpgsql
grammar close, we don't categorize keywords like we do in the main
grammar, so maybe what I'm saying doesn't make any sense.
yes, it's ok.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 1: 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
[PATCHES] EXECUTE USING for plpgsql (for 8.4)
Hello
this patch add USING clause into plpgsql EXECUTE statements.
Proposal:
http://archives.postgresql.org/pgsql-hackers/2007-10/msg00790.php
I found, so dynamics statements are little bit faster with parameters,
because we don't need call lot of in out/in functions. Mainly it is
barier to SQL injection.
I have question, who will be commiter of plpgsql region? I am quite
irritated from 8.3 process. Bruce's patch queue more or less black
hole, and I have not any idea, if somebody checking my patches or not
and if I have to be in readiness or not.
Patch queue is longer and longer, and I need to know any responsible
person who can be recipient of my reminder request. Really it's
nothing nice, if your work is repeatedly deleted or inserted to
current queue. Nobody can do any plans.
Best regards
Pavel Stehule
*** ./doc/src/sgml/plpgsql.sgml.orig 2007-10-22 10:31:57.0 +0200
--- ./doc/src/sgml/plpgsql.sgml 2007-10-22 12:04:52.0 +0200
***
*** 1005,1011
commandEXECUTE/command statement is provided:
synopsis
! EXECUTE replaceable class=commandcommand-string/replaceable optional INTO optionalSTRICT/optional replaceabletarget/replaceable /optional;
/synopsis
where replaceablecommand-string/replaceable is an expression
--- 1005,1011
commandEXECUTE/command statement is provided:
synopsis
! EXECUTE replaceable class=commandcommand-string/replaceable optional INTO optionalSTRICT/optional replaceabletarget/replaceable /optional optional USING replaceable class=parameterexpression/replaceable optional, .../optional /optional;
/synopsis
where replaceablecommand-string/replaceable is an expression
***
*** 1046,1051
--- 1046,1066
If the literalSTRICT/ option is given, an error is reported
unless the query produces exactly one row.
/para
+
+ para
+ The commandEXECUTE/command statement can take parameters. To refer
+ to the parameters use $1, $2, $3, etc. Any parameter have to be bind to
+ any variable or any expression with USING clause. You cannot use bind
+ arguments to pass the names of schema objects to a dynamic SQL statement.
+ The use of arguments is perfect protection from SQL injection.
+ programlisting
+ EXECUTE 'SELECT count(*) FROM '
+ || tabname::regclass
+ || ' WHERE inserted_by = $1 AND inserted = $2'
+INTO c
+USING checked_user, checked_date;
+ /programlisting
+ /para
para
commandSELECT INTO/command is not currently supported within
***
*** 1924,1930
rows:
synopsis
optional lt;lt;replaceablelabel/replaceablegt;gt; /optional
! FOR replaceabletarget/replaceable IN EXECUTE replaceabletext_expression/replaceable LOOP
replaceablestatements/replaceable
END LOOP optional replaceablelabel/replaceable /optional;
/synopsis
--- 1939,1945
rows:
synopsis
optional lt;lt;replaceablelabel/replaceablegt;gt; /optional
! FOR replaceabletarget/replaceable IN EXECUTE replaceabletext_expression/replaceable optional USING replaceable class=parameterexpression/replaceable optional, .../optional /optional LOOP
replaceablestatements/replaceable
END LOOP optional replaceablelabel/replaceable /optional;
/synopsis
*** ./src/pl/plpgsql/src/gram.y.orig 2007-10-16 10:44:47.0 +0200
--- ./src/pl/plpgsql/src/gram.y 2007-10-22 12:07:44.0 +0200
***
*** 21,26
--- 21,27
static PLpgSQL_expr *read_sql_construct(int until,
int until2,
+ int until3,
const char *expected,
const char *sqlstart,
bool isexpression,
***
*** 201,206
--- 202,208
%token K_THEN
%token K_TO
%token K_TYPE
+ %token K_USING
%token K_WARNING
%token K_WHEN
%token K_WHILE
***
*** 893,900
{
PLpgSQL_stmt_dynfors *new;
PLpgSQL_expr *expr;
! expr = plpgsql_read_expression(K_LOOP, LOOP);
new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
new-cmd_type = PLPGSQL_STMT_DYNFORS;
--- 895,908
{
PLpgSQL_stmt_dynfors *new;
PLpgSQL_expr *expr;
+ int term;
! expr = read_sql_construct(K_LOOP,
! K_USING,
! 0,
! LOOP|USING,
! SELECT ,
! true, true, term);
new = palloc0(sizeof(PLpgSQL_stmt_dynfors));
new-cmd_type = PLPGSQL_STMT_DYNFORS;
***
*** 921,926
--- 929,949
yyerror(loop variable of loop over rows must be a record or row variable or list of scalar variables);
}
new-query = expr;
+
+ if (term == K_USING)
+ {
+ for(;;)
+ {
+ int term;
+
+ expr = read_sql_construct(',', K_LOOP, 0, , or LOOP,
+ SELECT ,
+ true, true, term);
+ new-params = lappend
[PATCHES] actualised SQL/PSM patch
Hello New version of SQL/PSM patch is available. I am sending only link, because mail with attached patch was lost. Documentation is on available from http://www.pgsql.cz/index.php/SQL/PSM_Manual http://www.pgsql.cz/patches/plpgpsm83.diff.gz Regards Pavel Stehule ---(end of broadcast)--- TIP 1: 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: [PATCHES] WIP: updatable cursors in plpgsql
As the code stands plpgsql will try to issue something like UPDATE/DELETE ... WHERE CURRENT OF $1 Since we don't try to do anything with the cursor name until runtime, it seems that this would work if we allowed a parameter symbol there. Offhand that doesn't look hard. I tested it. It's great. Thank You Pavel Stehule ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] WIP: updatable cursors in plpgsql
Hello,
this small patch allows using updatable cursors in plpgsql.
Regards
Pavel Stehule
*** ./gram.y.orig 2007-06-11 10:43:09.0 +0200
--- ./gram.y 2007-06-11 11:48:03.0 +0200
***
*** 44,49
--- 44,50
static void plpgsql_sql_error_callback(void *arg);
static void check_labels(const char *start_label,
const char *end_label);
+ static void check_cursor_variable(void);
%}
***
*** 155,160
--- 156,162
%token K_CLOSE
%token K_CONSTANT
%token K_CONTINUE
+ %token K_CURRENTOF
%token K_CURSOR
%token K_DEBUG
%token K_DECLARE
***
*** 1527,1543
cursor_variable : T_SCALAR
{
! if (yylval.scalar-dtype != PLPGSQL_DTYPE_VAR)
! yyerror(cursor variable must be a simple variable);
!
! if (((PLpgSQL_var *) yylval.scalar)-datatype-typoid != REFCURSOROID)
! {
! plpgsql_error_lineno = plpgsql_scanner_lineno();
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg(\%s\ must be of type cursor or refcursor,
! ((PLpgSQL_var *) yylval.scalar)-refname)));
! }
$$ = (PLpgSQL_var *) yylval.scalar;
}
;
--- 1529,1535
cursor_variable : T_SCALAR
{
! check_cursor_variable();
$$ = (PLpgSQL_var *) yylval.scalar;
}
;
***
*** 1923,1928
--- 1915,1937
return result;
}
+
+ static void
+ check_cursor_variable(void)
+ {
+ if (yylval.scalar-dtype != PLPGSQL_DTYPE_VAR)
+ yyerror(cursor variable must be a simple variable);
+
+ if (((PLpgSQL_var *) yylval.scalar)-datatype-typoid != REFCURSOROID)
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg(\%s\ must be of type cursor or refcursor,
+ ((PLpgSQL_var *) yylval.scalar)-refname)));
+ }
+ }
+
static PLpgSQL_stmt *
make_execsql_stmt(const char *sqlstart, int lineno)
{
***
*** 1982,1987
--- 1991,2004
params, nparams));
plpgsql_dstring_append(ds, buf);
break;
+
+ case K_CURRENTOF:
+ if (yylex() != T_SCALAR)
+ yyerror(missing cursor variable);
+ check_cursor_variable();
+ plpgsql_dstring_append(ds, CURRENT OF );
+ plpgsql_dstring_append(ds, yytext);
+ break;
default:
plpgsql_dstring_append(ds, yytext);
*** ./scan.l.orig 2007-06-11 10:43:05.0 +0200
--- ./scan.l 2007-06-11 11:48:40.0 +0200
***
*** 119,124
--- 119,125
constant { return K_CONSTANT; }
continue { return K_CONTINUE; }
cursor { return K_CURSOR; }
+ current{space}+of { return K_CURRENTOF; }
debug { return K_DEBUG; }
declare { return K_DECLARE; }
default { return K_DEFAULT; }
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [PATCHES] WIP: updatable cursors in plpgsql
2007/6/11, Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: this small patch allows using updatable cursors in plpgsql. Why do we need this? For stored procedures. Updatable cursors are used mainly in transform procedures, and without suppport in plpgsql, you have to write external procedure. It similar with support scrollable cursors, which was added into plpgsql now. It's not strong argument. With this patch will be less diference between cursors supported by PostgreSQL and cursors in plpgsql. Updatable cursor are currently substituted using ctid, but updatable cursors are more clean and readable. PL/pgSQL can be consistent in support PostgreSQL SQL statements. It's little bit strange, PostgreSQL offer some functionality, which cannot be used from PL/pgSQL. Regards Pavel Stehule ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] WIP: updatable cursors in plpgsql
No, the question is what is the patch trying to accomplish, because as far as I can see it's wrong. It seems to be trying to insert the plpgsql name of the cursor, which is not necessarily the SQL name. All explicit cursors (what I know) use named SQL cursors. SQL name is checked in OPEN statement. Refcursors are problematic. But refcursors are not updatable. We have to check this case and raise error. It's correct - holdable cursors aren't updatable. It's WIP patch Regards Pavel Stehule ---(end of broadcast)--- TIP 1: 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
[PATCHES] pg_standby remove unsupported switch u
Hello this patch removes unsupported switch u from description for command. Regards Pavel Stehule *** ./contrib/pg_standby/pg_standby.c.orig 2007-05-22 15:47:42.0 +0200 --- ./contrib/pg_standby/pg_standby.c 2007-05-22 15:48:10.0 +0200 *** *** 379,385 fprintf(stderr, note space between [ARCHIVELOCATION] and [NEXTWALFILE]\n); fprintf(stderr, with main intended use via restore_command in the recovery.conf\n); fprintf(stderr, restore_command = 'pg_standby [OPTION]... [ARCHIVELOCATION] %%f %%p'\n); ! fprintf(stderr, e.g. restore_command = 'pg_standby -l -u /mnt/server/archiverdir %%f %%p'\n); fprintf(stderr, \nOptions:\n); fprintf(stderr, -c copies file from archive (default)\n); fprintf(stderr, -d generate lots of debugging output (testing only)\n); --- 379,385 fprintf(stderr, note space between [ARCHIVELOCATION] and [NEXTWALFILE]\n); fprintf(stderr, with main intended use via restore_command in the recovery.conf\n); fprintf(stderr, restore_command = 'pg_standby [OPTION]... [ARCHIVELOCATION] %%f %%p'\n); ! fprintf(stderr, e.g. restore_command = 'pg_standby -l /mnt/server/archiverdir %%f %%p'\n); fprintf(stderr, \nOptions:\n); fprintf(stderr, -c copies file from archive (default)\n); fprintf(stderr, -d generate lots of debugging output (testing only)\n); ---(end of broadcast)--- TIP 1: 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: [PATCHES] Updateable cursors patch
2007/5/18, FAST PostgreSQL [EMAIL PROTECTED]: Right. The current implementation allows only simple queries. Joins are disallowed. According to the standard, updateable cursors cannot be scrollable. So maybe I should put an explicit check during cursor creation disallowing scrollable updateable cursors. I am for it. It good protection before strange bugs Pavel Rgds, Arul Shaji Pavel Stehule wrote: 2007/5/18, FAST PostgreSQL [EMAIL PROTECTED]: No. It works with scrollable cursors. It will work for cursors/selects which does not put the results in some store, such as WITH hold/group by/order by etc But most of these restrictions apply for normal 'Select for update' anyway. (With the order by clause, the implementation is as per the sql standards.) some scrollable cursors are materialised. It depends on query :-(. Simple query without join can works. regards Pavel I can update the documentation once the initial review is done and what I have done gets atleast a pass mark :-) Rgds, Arul Shaji Pavel Stehule wrote: Hello I am not sure, but your solution will faill on scrollable cursors (it's similar to holdable cursors). I miss part about limits in documentation. Propably updatable cursors aren't supported by plpgsql (and it's point to ToDo). Regards Pavel Stehule 2007/5/17, FAST PostgreSQL [EMAIL PROTECTED]: Attached is an updated version of the updateable cursors patch against the latest cvs head. Most of the changes in the patch are to make it sync with the changes in CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by Jaime in his mail below. Rgds, Arul Shaji FAST PostgreSQL wrote: Right. I will send an updated patch against the CVS head in the next couple of days. Jaime Casanova wrote: On 4/4/07, FAST PostgreSQL [EMAIL PROTECTED] wrote: Attached is a working updateable cursors patch. The core functionality has been implemented and the patch also contains the regression tests and documentation. this one doesn't apply cleanly to HEAD because of the changes in http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: 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: [PATCHES] Updateable cursors patch
Hello I am not sure, but your solution will faill on scrollable cursors (it's similar to holdable cursors). I miss part about limits in documentation. Propably updatable cursors aren't supported by plpgsql (and it's point to ToDo). Regards Pavel Stehule 2007/5/17, FAST PostgreSQL [EMAIL PROTECTED]: Attached is an updated version of the updateable cursors patch against the latest cvs head. Most of the changes in the patch are to make it sync with the changes in CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by Jaime in his mail below. Rgds, Arul Shaji FAST PostgreSQL wrote: Right. I will send an updated patch against the CVS head in the next couple of days. Jaime Casanova wrote: On 4/4/07, FAST PostgreSQL [EMAIL PROTECTED] wrote: Attached is a working updateable cursors patch. The core functionality has been implemented and the patch also contains the regression tests and documentation. this one doesn't apply cleanly to HEAD because of the changes in http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Updateable cursors patch
2007/5/18, FAST PostgreSQL [EMAIL PROTECTED]: No. It works with scrollable cursors. It will work for cursors/selects which does not put the results in some store, such as WITH hold/group by/order by etc But most of these restrictions apply for normal 'Select for update' anyway. (With the order by clause, the implementation is as per the sql standards.) some scrollable cursors are materialised. It depends on query :-(. Simple query without join can works. regards Pavel I can update the documentation once the initial review is done and what I have done gets atleast a pass mark :-) Rgds, Arul Shaji Pavel Stehule wrote: Hello I am not sure, but your solution will faill on scrollable cursors (it's similar to holdable cursors). I miss part about limits in documentation. Propably updatable cursors aren't supported by plpgsql (and it's point to ToDo). Regards Pavel Stehule 2007/5/17, FAST PostgreSQL [EMAIL PROTECTED]: Attached is an updated version of the updateable cursors patch against the latest cvs head. Most of the changes in the patch are to make it sync with the changes in CVS recently, for DECLARE CURSOR and EXPLAIN, as mentioned by Jaime in his mail below. Rgds, Arul Shaji FAST PostgreSQL wrote: Right. I will send an updated patch against the CVS head in the next couple of days. Jaime Casanova wrote: On 4/4/07, FAST PostgreSQL [EMAIL PROTECTED] wrote: Attached is a working updateable cursors patch. The core functionality has been implemented and the patch also contains the regression tests and documentation. this one doesn't apply cleanly to HEAD because of the changes in http://archives.postgresql.org/pgsql-committers/2007-04/msg00447.php ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] actualised forgotten Magnus's patch for plpgsql MOVE statement
Do we have a patch to make this consistent? no, not yet. It's topic for discussion and ToDo Regards Pavel Stehule ---(end of broadcast)--- TIP 1: 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
[PATCHES] refreshed table function support
Hello
This patch contains implementation of table functions defined in ANSI
SQL 2003 (Conformance with SQL2003: T326 Table functions). Patch adds
new proargmode PROARGMODE_TABLE (based on PROARGMODE_OUT). Columns of
output table are similar OUT arguments, but don't generate any
variables.
Proposal: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php
Description: SIGMOD Record, Vol. 33, No. 1, March 2004
Patch contains three diffs:
* pgdumpclean .. small readability fix for pg_dump.c
* tabfce .. implementation of table functions
* plpgsql-tabfce .. table expression support for plpgsql's return stmt
This patch can broke applications which scan argmodes. Plpgsql support
is separated because plpgsql implementation isn't standardised. But
current plpgsql hasn't any method for one statement table output from
function, and table expression is clean and simple tool for it.
Older version was broken by enum patch and scrollable cursor patch.
Older version didn't dump table functions correctly. It's fixed now.
Regards
Pavel Stehule
*** ./pg_dump.c.orig 2007-05-06 11:13:11.0 +0200
--- ./pg_dump.c 2007-05-06 11:16:53.0 +0200
***
*** 5959,5971
{
switch (argmodes[j][0])
{
! case 'i':
argmode = ;
break;
! case 'o':
argmode = OUT ;
break;
! case 'b':
argmode = INOUT ;
break;
default:
--- 5959,5971
{
switch (argmodes[j][0])
{
! case PROARGMODE_IN:
argmode = ;
break;
! case PROARGMODE_OUT:
argmode = OUT ;
break;
! case PROARGMODE_INOUT:
argmode = INOUT ;
break;
default:
*** ./doc/src/sgml/ref/create_function.sgml.orig 2007-04-23 18:52:53.0 +0200
--- ./doc/src/sgml/ref/create_function.sgml 2007-05-06 10:00:58.0 +0200
***
*** 21,27
synopsis
CREATE [ OR REPLACE ] FUNCTION
replaceable class=parametername/replaceable ( [ [ replaceable class=parameterargmode/replaceable ] [ replaceable class=parameterargname/replaceable ] replaceable class=parameterargtype/replaceable [, ...] ] )
! [ RETURNS replaceable class=parameterrettype/replaceable ]
{ LANGUAGE replaceable class=parameterlangname/replaceable
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
--- 21,28
synopsis
CREATE [ OR REPLACE ] FUNCTION
replaceable class=parametername/replaceable ( [ [ replaceable class=parameterargmode/replaceable ] [ replaceable class=parameterargname/replaceable ] replaceable class=parameterargtype/replaceable [, ...] ] )
! [ RETURNS replaceable class=parameterrettype/replaceable
! | RETURNS TABLE ( replaceable class=parametercolname/replaceable replaceable class=parametercoltype/replaceable [, ...] ) ]
{ LANGUAGE replaceable class=parameterlangname/replaceable
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
***
*** 387,392
--- 388,427
/listitem
/varlistentry
+ varlistentry
+ termreplaceable class=parametercolname/replaceable/term
+
+ listitem
+ para
+The name of an output table column.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
+ termreplaceable class=parametercoltype/replaceable/term
+
+ listitem
+ para
+The data type(s) of output table column.
+ /para
+ para
+Depending on the implementation language it might also be allowed
+to specify quotepseudotypes/ such as typecstring/.
+Pseudotypes indicate that the actual argument type is either
+incompletely specified, or outside the set of ordinary SQL data types.
+ /para
+ para
+The type of a column is referenced by writing
+literalreplaceable
+class=parametertablename/replaceable.replaceable
+class=parametercolumnname/replaceable%TYPE/literal.
+Using this feature can sometimes help make a function independent of
+changes to the definition of a table.
+ /para
+ /listitem
+ /varlistentry
+
/variablelist
/refsect1
***
*** 564,570
A commandCREATE FUNCTION/command command is defined in SQL:1999 and later.
The productnamePostgreSQL/productname version is similar but
not fully compatible. The attributes are not portable, neither are the
!different available languages.
/para
para
--- 599,605
A commandCREATE FUNCTION/command command is defined in SQL:1999 and later.
The productnamePostgreSQL/productname version is similar but
not fully compatible. The attributes are not portable, neither are the
!different available languages. TABLE functions are defined in SQL:2003.
/para
para
*** ./doc/src/sgml/xfunc.sgml.orig 2007-05-06 09:54:41.0 +0200
--- ./doc/src/sgml
Re: [PATCHES] plpgpsm
Hello, I actualised sql/psm patch: added: * last Tom's plpgsql patches (some fixes and enum support) * scrollable cursor's support new version is on http://www.pgsql.cz/patches/plpgpsm0501.diff.gz Regard Pavel Stehule Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] actualised forgotten Magnus's patch for plpgsql MOVE statement
I would argue that we should likewise not allow them in plpgsql's MOVE, although this is more of a judgment call than is the case for FETCH. I just don't think it's a good idea to provide two redundant ways to do the same thing, when we might want to make one of the ways mean something else later. There's no upside and there might be a downside. It's question. There are lot of links to FETCH in doc, and we support from FETCH direction only subset. It needs at least notice in documentation. When I testeid MOVE I found an form MOVE FORWARD 10 ... more natural than MOVE RELATIVE 10 and if we support MOVE FORWARD ... then is logic support MOVE FORWARD n , else FORWARD, BACKWARD are nonstandard and MOVE statement too. Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PATCHES] actualised forgotten Magnus's patch for plpgsql MOVE statement
Hello
I refreshed Magnus's patch
http://archives.postgresql.org/pgsql-patches/2007-02/msg00275.php from
februar.
Regards
Pavel Stehule
p.s. scrollable cursors in plpgsql need little work still. I forgot for
nonstandard (postgresql extension) direction forward all, forward n,
backward n. Forward all propably hasn't sense.
_
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/
*** ./doc/src/sgml/plpgsql.sgml.orig 2007-04-20 09:01:50.0 +0200
--- ./doc/src/sgml/plpgsql.sgml 2007-04-20 09:09:04.0 +0200
***
*** 1524,1529
--- 1524,1536
/listitem
listitem
para
+ A commandMOVE/ statement sets literalFOUND/literal
+ true if is success, false if is out of table.
+/para
+ /listitem
+
+ listitem
+para
A commandFOR/ statement sets literalFOUND/literal true
if it iterates one or more times, else false. This applies to
all three variants of the commandFOR/ statement (integer
***
*** 2567,2572
--- 2574,2624
/sect3
sect3
+ titleliteralMOVE//title
+
+ synopsis
+ MOVE optional replaceabledirection/replaceable FROM /optional replaceablecursor/replaceable;
+ /synopsis
+
+ para
+ MOVE repositions a cursor without retrieving any data. MOVE works
+ exactly like the FETCH command, except it only positions the
+ cursor and does not return rows. As with commandSELECT
+ INTO/command, the special variable literalFOUND/literal can
+ be checked to see whether a cursor was repositioned or not.
+ /para
+
+ para
+ The replaceabledirection/replaceable clause can be any of the
+ variants allowed in the SQL xref linkend=sql-fetch
+ endterm=sql-fetch-title command except the ones that can fetch
+ more than one row; namely, it can be
+ literalNEXT/,
+ literalPRIOR/,
+ literalFIRST/,
+ literalLAST/,
+ literalABSOLUTE/ replaceablecount/replaceable,
+ literalRELATIVE/ replaceablecount/replaceable,
+ literalFORWARD/, or
+ literalBACKWARD/.
+ Omitting replaceabledirection/replaceable is the same
+ as specifying literalNEXT/.
+ replaceabledirection/replaceable values that require moving
+ backward are likely to fail unless the cursor was declared or opened
+ with the literalSCROLL/ option.
+ /para
+
+ para
+ Examples:
+ programlisting
+ MOVE curs1;
+ MOVE LAST FROM curs3;
+ MOVE RELATIVE -2 FROM curs4;
+ /programlisting
+/para
+ /sect3
+
+ sect3
titleliteralCLOSE//title
synopsis
*** ./src/pl/plpgsql/src/gram.y.orig 2007-04-19 19:15:28.0 +0200
--- ./src/pl/plpgsql/src/gram.y 2007-04-19 19:39:36.0 +0200
***
*** 125,131
%type stmt stmt_assign stmt_if stmt_loop stmt_while stmt_exit
%type stmt stmt_return stmt_raise stmt_execsql stmt_execsql_insert
%type stmt stmt_dynexecute stmt_for stmt_perform stmt_getdiag
! %type stmt stmt_open stmt_fetch stmt_close stmt_null
%type list proc_exceptions
%type exception_block exception_sect
--- 125,131
%type stmt stmt_assign stmt_if stmt_loop stmt_while stmt_exit
%type stmt stmt_return stmt_raise stmt_execsql stmt_execsql_insert
%type stmt stmt_dynexecute stmt_for stmt_perform stmt_getdiag
! %type stmt stmt_open stmt_fetch stmt_move stmt_close stmt_null
%type list proc_exceptions
%type exception_block exception_sect
***
*** 179,184
--- 179,185
%token K_IS
%token K_LOG
%token K_LOOP
+ %token K_MOVE
%token K_NEXT
%token K_NOSCROLL
%token K_NOT
***
*** 635,640
--- 636,643
{ $$ = $1; }
| stmt_fetch
{ $$ = $1; }
+ | stmt_move
+ { $$ = $1; }
| stmt_close
{ $$ = $1; }
| stmt_null
***
*** 1478,1483
--- 1481,1499
fetch-rec = rec;
fetch-row = row;
fetch-curvar = $4-varno;
+ fetch-is_move = false;
+
+ $$ = (PLpgSQL_stmt *)fetch;
+ }
+ ;
+
+ stmt_move : K_MOVE lno opt_fetch_direction cursor_variable ';'
+ {
+ PLpgSQL_stmt_fetch *fetch = $3;
+
+ fetch-lineno = $2;
+ fetch-curvar = $4-varno;
+ fetch-is_move = true;
$$ = (PLpgSQL_stmt *)fetch;
}
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2007-04-20 09:24:27.0 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2007-04-20 09:25:14.0 +0200
***
*** 3112,3118
return PLPGSQL_RC_OK;
}
-
/* --
* exec_stmt_fetch Fetch from a cursor into a target
* --
--- 3112,3117
***
*** 3164,3208
}
/* --
! * Determine if we fetch into a record or a row
! * --
! */
! if (stmt-rec != NULL)
! rec
Re: [PATCHES] scrollable cursor support without MOVE statement
On Wed, 2007-03-28 at 17:42 +0200, Pavel Stehule wrote: This is the most recent email I have on this. Was the scrollable patch applied? If not, would you resubmit? I resubmit scrollable cursor patch I notice your patch has been accepted, though admit I hadn't noticed it previously. I resubmited this patch because Bruce removed it from queue instead of GUC protection patch Can I ask a question relating to the patch? How is the scrollability determined? Scrollable cursors and sorts don't mix very well in terms of performance, as you may know. Previously, since NOSCROLL was the only option, this wasn't a problem. Now that we have scrollable cursors, it is an issue, since according to the doc change the scrollability default is neither scroll nor noscroll. default is noscroll I'm concerned that many PL/pgSQL routines will now run slower because they may now be considered scrollable when they previously were not. How is the scrollability determined? Do we look at the kids of FETCH being used to determine whether we need scrolling? (which would be great) Or will we have to manually change all existing PL/pgSQL code so that it is definitely NOSCROLL? (which would be unacceptable). Or? default is without changes on functionality. Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Table function support
Hello I searched some notes about this topic. I didn't find any usefull sample. Lot of samples are about external stored procedures and others about using table expression like create function foo(i1) returns table (a1 int) as return table(select a1 from tab) isn't clear if table attributes are related to output variables, but nobody join it together. SQL/PSM sample: create function accounts_of (customer_name char(20)) returns table ( account_number char(10), branch_name char(15) balance numeric(12,2)) return table (select account_number, branch_name, balance from account A where exists ( select * from depositor D where D.customer_name = accounts_of.customer_name and D.account_number = A.account_number )) correct calling of it is: select * from table (accounts_of (Smith)) next sample: CREATE FUNCTION filmtyp (art CHAR(2)) RETURNS TABLE (titel VARCHAR(75), jahr INTEGER) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC RETURN SELECT titel, jahr FROM film WHERE film.art = filmtyp.art Table functions are named as parametrised views too. I don't thing using OUT variables is good idea, because you will have problems with colum's names, which is problem for plpgsql. http://www.wiscorp.com/SQL2003Features.pdf http://wwwdvs.informatik.uni-kl.de/courses/NEDM/SS2004/Vorlesungsunterlagen/NEDM.Chapter.03.User-defined_Routines_and_Object_Behavior.pdf Regards Pavel Stehule From: Tom Lane [EMAIL PROTECTED] To: Pavel Stehule [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [PATCHES] Table function support Date: Tue, 10 Apr 2007 18:17:14 -0400 Pavel Stehule [EMAIL PROTECTED] writes: this patch allows using SQL2003 syntax for set returning functions. It is based on using new type of argmode - PROARGMODE_TABLE. I've been looking at this, and my feeling is that we should drop the PROARGMODE_TABLE business and just define RETURNS TABLE(x int, y int) as exactly equivalent to RETURNS SETOF RECORD with x and y treated as OUT parameters. There isn't any advantage to distinguishing the cases that outweighs breaking client code that looks at pg_proc.proargmodes. I don't believe that the SQL spec prevents us from exposing those parameter names to PL functions, especially since none of our PLs are in the standard at all. regards, tom lane _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Table function support
I've been looking at this, and my feeling is that we should drop the PROARGMODE_TABLE business and just define RETURNS TABLE(x int, y int) as exactly equivalent to RETURNS SETOF RECORD with x and y treated as OUT parameters. There isn't any advantage to distinguishing the cases that outweighs breaking client code that looks at pg_proc.proargmodes. I don't believe that the SQL spec prevents us from exposing those parameter names to PL functions, especially since none of our PLs are in the standard at all. Reason for PROARGMODE_TABLE was protection before name's collision, and x, and y are table attributies (not variables) and then we are protected before collision. It's shortcut for create function foo() returns setof record as ... select * from foo() as (x int, y int); Regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Table function support
I've been looking at this, and my feeling is that we should drop the PROARGMODE_TABLE business and just define RETURNS TABLE(x int, y int) as exactly equivalent to RETURNS SETOF RECORD with x and y treated as OUT parameters. There isn't any advantage to distinguishing the cases that outweighs breaking client code that looks at pg_proc.proargmodes. I don't believe that the SQL spec prevents us from exposing those parameter names to PL functions, especially since none of our PLs are in the standard at all. Reason for PROARGMODE_TABLE was protection before name's collision, and x, and y are table attributies (not variables) and then we are protected before collision. It's shortcut for create function foo() returns setof record as ... select * from foo() as (x int, y int); Regards Pavel Stehule _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] simply custom variables protection
Hello Bruce My patch allows to allert somebody so any custom variable is protected. I dont understand Tom's arguments. Probably this patch do more than is necessary. Really important for protection is only calling ResetPGVariable() function. My funcionality has only information value. Regards Pavel Stehule From: Bruce Momjian [EMAIL PROTECTED] To: Pavel Stehule [EMAIL PROTECTED] CC: pgsql-patches@postgresql.org, [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: [PATCHES] simply custom variables protection Date: Sat, 7 Apr 2007 11:54:13 -0400 (EDT) Pavel, would you remind me how this is useful? --- Pavel Stehule wrote: Hello this patch contains function ArmorCustomVariables. This function set flag armored on any custom variable. From this moment only superuser can change this variable. p.s. use it together with ResetPGVariable() Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ [ Attachment, skipping... ] ---(end of broadcast)--- TIP 1: 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 -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] simply custom variables protection
How does a user protect a custom variable using your code? I don't see any API that would allow that. Every module is responsibile for protectiong own custom variables. Only module knows if some variable needs protection. And after module inicialisation module can call ArmorCustomVariable function. From this moment only superuser can modify this custom variable. If it call ResetPGVariable() function before then default value is protected. It's question if test for superuser is necessery, I hope so it's usefull and I have posibility write security definer function where I can safely modify custom variables. --- Regards Pavel Stehule From: Bruce Momjian [EMAIL PROTECTED] To: Pavel Stehule [EMAIL PROTECTED] CC: pgsql-patches@postgresql.org, [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: [PATCHES] simply custom variables protection Date: Sat, 7 Apr 2007 11:54:13 -0400 (EDT) Pavel, would you remind me how this is useful? --- Pavel Stehule wrote: Hello this patch contains function ArmorCustomVariables. This function set flag armored on any custom variable. From this moment only superuser can change this variable. p.s. use it together with ResetPGVariable() Regards Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ [ Attachment, skipping... ] ---(end of broadcast)--- TIP 1: 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 -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] simply custom variables protection
Furthermore I believe the patch is incomplete/wrong, because it adds only one check on the armored flag, whereas PGC_SUSET affects behavior in a number of places. I also notice that it will make setting of a an armored custom variable from postgresql.conf fail outright in non-superuser sessions, which is surely not desirable. I don't protect this patch. I didn't understand original proposal well. Tom, I don't understand your last notice. Can you explain it, please. Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PATCHES] plpgpsm
Hello I have problem with sending propably too much large patch, so I am sending only link to patch. Is it correct? I sent this patch two times without success. This patch add new PL language to PostgreSQL. You can enable it by setting --with-sqlpsm in configure. Proposal: http://archives.postgresql.org/pgsql-hackers/2007-03/msg01487.php link to patch: http://www.pgsql.cz/patches/plpgpsm.diff.gz Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PATCHES] scrollable cursor support without MOVE statement
This is the most recent email I have on this. Was the scrollable patch applied? If not, would you resubmit? I resubmit scrollable cursor patch Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ *** ./doc/src/sgml/plpgsql.sgml.orig 2007-01-26 20:30:17.0 +0100 --- ./doc/src/sgml/plpgsql.sgml 2007-01-26 21:33:38.0 +0100 *** *** 2354,2360 internally to avoid memory problems.) A more interesting usage is to return a reference to a cursor that a function has created, allowing the caller to read the rows. This provides an efficient way to return ! large row sets from functions. /para sect2 id=plpgsql-cursor-declarations --- 2354,2361 internally to avoid memory problems.) A more interesting usage is to return a reference to a cursor that a function has created, allowing the caller to read the rows. This provides an efficient way to return ! large row sets from functions. PL/pgSQL allows to use scrollable ! cursors. /para sect2 id=plpgsql-cursor-declarations *** *** 2368,2374 Another way is to use the cursor declaration syntax, which in general is: synopsis ! replaceablename/replaceable CURSOR optional ( replaceablearguments/replaceable ) /optional FOR replaceablequery/replaceable; /synopsis (literalFOR/ may be replaced by literalIS/ for productnameOracle/productname compatibility.) --- 2369,2375 Another way is to use the cursor declaration syntax, which in general is: synopsis ! replaceablename/replaceable optional optional NO /optional SCROLL /optional CURSOR optional ( replaceablearguments/replaceable ) /optional FOR replaceablequery/replaceable; /synopsis (literalFOR/ may be replaced by literalIS/ for productnameOracle/productname compatibility.) *** *** 2517,2523 titleliteralFETCH//title synopsis ! FETCH replaceablecursor/replaceable INTO replaceabletarget/replaceable; /synopsis para --- 2518,2524 titleliteralFETCH//title synopsis ! FETCH optional replaceabledirection/replaceable FROM /optional replaceablecursor/replaceable INTO replaceabletarget/replaceable; /synopsis para *** *** 2526,2539 variable, or a comma-separated list of simple variables, just like commandSELECT INTO/command. As with commandSELECT INTO/command, the special variable literalFOUND/literal may ! be checked to see whether a row was obtained or not. /para - para An example: programlisting FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz; /programlisting /para /sect3 --- 2527,2545 variable, or a comma-separated list of simple variables, just like commandSELECT INTO/command. As with commandSELECT INTO/command, the special variable literalFOUND/literal may ! be checked to see whether a row was obtained or not. More details ! about replaceabledirection/replaceable you can find in ! xref linkend=sql-fetch without literalBACKWARD/ and literalFORWARD/ keywords. ! Statement commandFETCH/command in applicationPL/pgSQL/ returns only one ! or zero row every time. /para para An example: programlisting FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz; + FETCH LAST INTO x, y; + FETCH RELATIVE -2 INTO x; /programlisting /para /sect3 *** ./doc/src/sgml/spi.sgml.orig 2007-01-14 12:37:19.0 +0100 --- ./doc/src/sgml/spi.sgml 2007-01-26 11:46:18.0 +0100 *** *** 800,805 --- 800,937 !-- *** -- + refentry id=spi-spi-prepare-cursor + refmeta + refentrytitleSPI_prepare_cursor/refentrytitle + /refmeta + + refnamediv + refnameSPI_prepare_cursor/refname + refpurposeprepare a plan for a cursor, without executing it yet/refpurpose + /refnamediv + + indextermprimarySPI_prepare_cursor/primary/indexterm + + refsynopsisdiv + synopsis + void * SPI_prepare_cursor(const char * parametercommand/parameter, int parameternargs/parameter, Oid * parameterargtypes/parameter, int parameteroptions/parameter) + /synopsis + /refsynopsisdiv + + refsect1 + titleDescription/title + + para +functionSPI_prepare_cursor/function creates and returns an execution +plan for the specified select but doesn't execute the command. +This function should only be called from a connected procedure. This +function allows set cursor's options. + /para + + para +When the same or a similar command is to be executed repeatedly, it +may be advantageous to perform the planning only once. +functionSPI_prepare_cursor
Re: [PATCHES] simply custom variables protection
Pavel Stehule [EMAIL PROTECTED] writes: this patch contains function ArmorCustomVariables. This function set flag armored on any custom variable. From this moment only superuser can change this variable. Why is this a good idea? Why don't you just fix the problem as previously agreed, namely make the GUC context values work properly for custom variables? I am sorry, I don't see it. In my solution module knows own variables and can chose what want to do with its. So if I like ro variables, then I add into module init calling ResetPgVariables() and ArmorCustomVariables(), and without anything the behave is same like current.What do you though. Regards Pavel Stehule _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PATCHES] correct format for date, time, timestamp for XML functionality
Hello,
this patch ensures independency datetime fields on current datestyle
setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same to
USE_ISO_DATESTYLE. Differences are for timestamp:
ISO: -mm-dd hh24:mi:ss
XSD: -mm-ddThh24:mi:ss
I found one link about this topic:
http://forums.oracle.com/forums/thread.jspa?threadID=467278tstart=0
Regards
Pavel Stehule
_
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/
*** ./src/backend/utils/adt/datetime.c.orig 2007-02-19 21:46:54.0 +0100
--- ./src/backend/utils/adt/datetime.c 2007-02-19 22:06:20.0 +0100
***
*** 3188,3193
--- 3188,3194
switch (style)
{
case USE_ISO_DATES:
+ case USE_XSD_DATES:
/* compatible with ISO date formats */
if (tm-tm_year 0)
sprintf(str, %04d-%02d-%02d,
***
*** 3278,3283
--- 3279,3285
* SQL - mm/dd/ hh:mm:ss.ss tz
* ISO - -mm-dd hh:mm:ss+/-tz
* German - dd.mm. hh:mm:ss tz
+ * XSD - -mm-ddThh:mm:ss.ss+/-tz
* Variants (affects order of month and day for Postgres and SQL styles):
* US - mm/dd/
* European - dd/mm/
***
*** 3296,3306
switch (style)
{
case USE_ISO_DATES:
/* Compatible with ISO-8601 date formats */
! sprintf(str, %04d-%02d-%02d %02d:%02d,
(tm-tm_year 0) ? tm-tm_year : -(tm-tm_year - 1),
tm-tm_mon, tm-tm_mday, tm-tm_hour, tm-tm_min);
/*
* Print fractional seconds if any. The field widths here should
--- 3298,3315
switch (style)
{
case USE_ISO_DATES:
+ case USE_XSD_DATES:
/* Compatible with ISO-8601 date formats */
! if (style == USE_ISO_DATES)
! sprintf(str, %04d-%02d-%02d %02d:%02d,
(tm-tm_year 0) ? tm-tm_year : -(tm-tm_year - 1),
tm-tm_mon, tm-tm_mday, tm-tm_hour, tm-tm_min);
+ else
+ sprintf(str, %04d-%02d-%02dT%02d:%02d,
+ (tm-tm_year 0) ? tm-tm_year : -(tm-tm_year - 1),
+ tm-tm_mon, tm-tm_mday, tm-tm_hour, tm-tm_min);
+
/*
* Print fractional seconds if any. The field widths here should
*** ./src/backend/utils/adt/xml.c.orig 2007-02-19 19:37:27.0 +0100
--- ./src/backend/utils/adt/xml.c 2007-02-19 22:33:11.0 +0100
***
*** 65,73
#include utils/builtins.h
#include utils/lsyscache.h
#include utils/memutils.h
#include utils/xml.h
-
#ifdef USE_LIBXML
static StringInfo xml_err_buf = NULL;
--- 65,74
#include utils/builtins.h
#include utils/lsyscache.h
#include utils/memutils.h
+ #include utils/date.h
+ #include utils/datetime.h
#include utils/xml.h
#ifdef USE_LIBXML
static StringInfo xml_err_buf = NULL;
***
*** 1513,1526
bool isvarlena;
char *p, *str;
! if (type == BOOLOID)
{
! if (DatumGetBool(value))
! return true;
! else
! return false;
! }
getTypeOutputInfo(type, typeOut, isvarlena);
str = OidOutputFunctionCall(typeOut, value);
--- 1514,1595
bool isvarlena;
char *p, *str;
! /* xsd format doesn't depend on current settings */
! switch (type)
{
! case BOOLOID:
! if (DatumGetBool(value))
! return true;
! else
! return false;
! case DATEOID:
! {
! struct pg_tm tt,
! *tm = tt;
! charbuf[MAXDATELEN + 1];
! DateADT date = DatumGetDateADT(value);
!
! j2date(date + POSTGRES_EPOCH_JDATE,
! (tm-tm_year), (tm-tm_mon), (tm-tm_mday));
!
! EncodeDateOnly(tm, USE_XSD_DATES, buf);
! return pstrdup(buf);
! }
!
! case TIMEOID:
! /* datestyle hasn't affect on time formating */
! break;
!
! case TIMESTAMPOID:
! {
! Timestamp timestamp = DatumGetTimestamp(value);
! struct pg_tm tt,
! *tm = tt;
! fsec_t fsec;
! char *tzn = NULL;
! charbuf[MAXDATELEN + 1];
!
! /* xsd doesn't support infinite values */
! if (TIMESTAMP_NOT_FINITE(timestamp))
! ereport(ERROR,
! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
! errmsg(timestamp out of range)));
! else if (timestamp2tm(timestamp, NULL, tm, fsec, NULL, NULL) == 0)
! EncodeDateTime(tm, fsec, NULL, tzn, USE_XSD_DATES, buf);
! else
! ereport(ERROR,
! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
Pavel Stehule [EMAIL PROTECTED] writes: this patch ensures independency datetime fields on current datestyle setting. Add new internal datestyle USE_XSD_DATESTYLE. It's almoust same to USE_ISO_DATESTYLE. Differences are for timestamp: ISO: -mm-dd hh24:mi:ss XSD: -mm-ddThh24:mi:ss Why is that a good idea? Even if some standard out there mandates the 'T', I'd bet lunch that the other format is a whole lot more portable. if you use xsd schema then situation is clear. I have to respect it, because xsd:datetime is well defined and SQL/XML expect respecting xsd. If I don't use xsd schema, then I teoreticly can put date in any format. Sample: we use german format, but everybody with good mind doesn't use it for xml, because then he cannot use xml validation based on xsd schema. xsd format use Oracle 10g: http://forums.oracle.com/forums/thread.jspa?threadID=467278tstart=0 9x used ISO format, which is (however) invalid. http://books.xmlschemata.org/relaxng/ch19-77049.html Currently without this patch PostgreSQL generate invalalid xml documents. That is all. My patch doesn't protect any output. Simply use cast to text, or to_char fce. nice a day Pavel Stehule _ Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
I'm not sure that we are actually guaranteeing anything about XML validity against any schema or DTD, are we? what? ofcourse you cannot garant validity against any schema. But mapping functions are standardised and expect xsd. And I what I can meet protocols based on xml, they respect xsd everywhere. I repeat. When XML functions don't produce XML schema, then all is possible. But using xsd standard is safe way (like ISO format in SQL world). With nested XML schema whitch is related to xsd there isn't any different way. The best solution is validation XML before output to client. pg have to produce everytime valid xml If there was previous email I apologise, as I didn't find it when I looked. Perhaps in such cases you could include a ref to the archive URL. cheers andrew _ With tax season right around the corner, make sure to follow these few simple tips. http://articles.moneycentral.msn.com/Taxes/PreparationTips/PreparationTips.aspx?icid=HMFebtagline ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] correct format for date, time, timestamp for XML functionality
On Tue, Feb 20, 2007 at 04:32:28PM +0100, Pavel Stehule wrote: I am sorry. I reported this two times before. This patch is related only for xml functionality. XSD datestyle is only one internal constant. There are no new datestyle (I hope so can be usefull). My patch is small bug fix like Peter's patch for boolean datatype. Generating invalid xml is bug not feature, no? If it's just for XSD, perhaps it should be a part of the XML output functionality instead of being a global datestyle? I share code and I needed one safe enum value. That's all. There isn't new global datestyle. I didn't want to duplicate code from timestamp.c and date.c. Pavel _ Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/ ---(end of broadcast)--- TIP 1: 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: [PATCHES] correct format for date, time, timestamp for XML functionality
From: Andrew Dunstan [EMAIL PROTECTED] To: Peter Eisentraut [EMAIL PROTECTED] CC: Pavel Stehule [EMAIL PROTECTED], pgsql-patches@postgresql.org Subject: Re: [PATCHES] correct format for date, time, timestamp for XML functionality Date: Tue, 20 Feb 2007 11:37:31 -0500 Peter Eisentraut wrote: Am Dienstag, 20. Februar 2007 16:54 schrieb Andrew Dunstan: I'm not sure that we are actually guaranteeing anything about XML validity against any schema or DTD, are we? That is the xmlschema part of table_to_xmlschema() et al. recently discussed. That entire functionality hinges on producing output that validates against XML Schema schemas, and so we cannot pick the data type formats outselves. Then why would we use a setting to govern this? Should we not simply ensure that we always output timestamps in XML using the correct ISO8601 format? xsd knows datetime type. You can inherit it, and then you have to respect it. You can do own type, but you lost information, and any general scripts don't understand. I don't know why xsd doesn't iso format, but its simply fact. Please read this discussion: http://forums.oracle.com/forums/thread.jspa?threadID=467278tstart=0 cheers andrew _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] Table function support
Hello
this patch allows using SQL2003 syntax for set returning functions. It is
based on using new type of argmode - PROARGMODE_TABLE.
Proposal: http://archives.postgresql.org/pgsql-hackers/2007-02/msg00318.php
Sample:
CREATE FUNCTION foof(a int)
RETURNS TABLE(a int, b int) AS
$$ SELECT x, y FROM Foo WHERE x a $$ LANGUAGE sql;
CREATE FUNCTION fooff(a int)
RETURNS TABLE(a int, b int) AS $$
BEGIN
RETURN TABLE(SELECT * FRON Foo WHERE x a);
END; $$ LANGUAGE plpgsql;
This patch enhance plpgsql stmt return too (table expression support).
Conformance with SQL2003:
T326Table functions
Description: SIGMOD Record, Vol. 33, No. 1, March 2004
Regards
Pavel Stehule
_
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/
*** ./doc/src/sgml/plpgsql.sgml.orig 2007-02-11 12:01:48.0 +0100
--- ./doc/src/sgml/plpgsql.sgml 2007-02-11 18:54:16.0 +0100
***
*** 1567,1575
sect3
titlecommandRETURN//title
! synopsis
! RETURN replaceableexpression/replaceable;
! /synopsis
para
commandRETURN/command with an expression terminates the
--- 1567,1580
sect3
titlecommandRETURN//title
! itemizedlist
! listitem
! paraliteralRETURN replaceableexpression/replaceable;//
! /listitem
! listitem
! paraliteralRETURN TABLE ( replaceablequery/replaceable );//
! /listitem
! /itemizedlist
para
commandRETURN/command with an expression terminates the
***
*** 1594,1599
--- 1599,1613
/para
para
+ If your function returns a set, you can use table expression.
+ An replaceablequery/replaceable is evaluated and result set
+ is returned to the caller. You cannot mix commandRETURN TABLE/command
+ and commandRETURN NEXT/command statements in one function.
+ Performance might be poor, if result set is very large. The reason
+ is described below.
+ /para
+
+ para
If you declared the function to return typevoid/type, a
commandRETURN/command statement can be used to exit the function
early; but do not write an expression following
*** ./doc/src/sgml/ref/create_function.sgml.orig 2007-02-11 09:55:06.0 +0100
--- ./doc/src/sgml/ref/create_function.sgml 2007-02-11 10:28:08.0 +0100
***
*** 21,27
synopsis
CREATE [ OR REPLACE ] FUNCTION
replaceable class=parametername/replaceable ( [ [ replaceable class=parameterargmode/replaceable ] [ replaceable class=parameterargname/replaceable ] replaceable class=parameterargtype/replaceable [, ...] ] )
! [ RETURNS replaceable class=parameterrettype/replaceable ]
{ LANGUAGE replaceable class=parameterlangname/replaceable
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
--- 21,28
synopsis
CREATE [ OR REPLACE ] FUNCTION
replaceable class=parametername/replaceable ( [ [ replaceable class=parameterargmode/replaceable ] [ replaceable class=parameterargname/replaceable ] replaceable class=parameterargtype/replaceable [, ...] ] )
! [ RETURNS replaceable class=parameterrettype/replaceable
! | RETURNS TABLE ( replaceable class=parametercolname/replaceable replaceable class=parametercoltype/replaceable [, ...] ) ]
{ LANGUAGE replaceable class=parameterlangname/replaceable
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
***
*** 387,392
--- 388,427
/listitem
/varlistentry
+ varlistentry
+ termreplaceable class=parametercolname/replaceable/term
+
+ listitem
+ para
+The name of an output table column.
+ /para
+ /listitem
+ /varlistentry
+
+ varlistentry
+ termreplaceable class=parametercoltype/replaceable/term
+
+ listitem
+ para
+The data type(s) of output table column.
+ /para
+ para
+Depending on the implementation language it might also be allowed
+to specify quotepseudotypes/ such as typecstring/.
+Pseudotypes indicate that the actual argument type is either
+incompletely specified, or outside the set of ordinary SQL data types.
+ /para
+ para
+The type of a column is referenced by writing
+literalreplaceable
+class=parametertablename/replaceable.replaceable
+class=parametercolumnname/replaceable%TYPE/literal.
+Using this feature can sometimes help make a function independent of
+changes to the definition of a table.
+ /para
+ /listitem
+ /varlistentry
+
/variablelist
/refsect1
***
*** 516,522
A commandCREATE FUNCTION/command command is defined in SQL:1999 and later.
The productnamePostgreSQL/productname version
