[HACKERS] 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 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] PostgreSQL Programmer's Guide Books
On Friday 25 January 2008 19:02, Gevik Babakhani wrote:
> Hi,
>
> I was wondering how accurate there books are (perhaps not so much) , if one
> wants to learn more about the internals?
> http://www.postgresql.org/docs/7.3/static/programmer.html
> http://www.cs.helsinki.fi/u/laine/postgresql/programmer/
>
>
These are basically old versions of the documentation, so thier relevance, and
what you can get out of them, probably is strictly dependent on how much the
code has changed in any given area. Note there are some style differences
that could make areas of the old docs more helpful (depending on thier
accuracy), but that's more a style thing:
http://www.cs.helsinki.fi/u/laine/postgresql/programmer/arch-pg.htm#PGARCH-CONNECTIONS
http://www.postgresql.org/docs/7.3/static/arch-pg.html#PGARCH-CONNECTIONS
http://www.postgresql.org/docs/8.3/static/connect-estab.html
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] PostgreSQL Programmer's Guide Books
thank you :)
> -Original Message-
> From: Robert Treat [mailto:[EMAIL PROTECTED]
> Sent: Saturday, January 26, 2008 4:29 PM
> To: pgsql-hackers@postgresql.org
> Cc: Gevik Babakhani
> Subject: Re: [HACKERS] PostgreSQL Programmer's Guide Books
>
> On Friday 25 January 2008 19:02, Gevik Babakhani wrote:
> > Hi,
> >
> > I was wondering how accurate there books are (perhaps not
> so much) ,
> > if one wants to learn more about the internals?
> > http://www.postgresql.org/docs/7.3/static/programmer.html
> > http://www.cs.helsinki.fi/u/laine/postgresql/programmer/
> >
> >
>
> These are basically old versions of the documentation, so
> thier relevance, and what you can get out of them, probably
> is strictly dependent on how much the code has changed in any
> given area. Note there are some style differences that could
> make areas of the old docs more helpful (depending on thier
> accuracy), but that's more a style thing:
>
> http://www.cs.helsinki.fi/u/laine/postgresql/programmer/arch-p
g.htm#PGARCH-CONNECTIONS
> http://www.postgresql.org/docs/7.3/static/arch-pg.html#PGARCH-
> CONNECTIONS
> http://www.postgresql.org/docs/8.3/static/connect-estab.html
>
> --
> Robert Treat
> Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
>
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Truncate Triggers
On Friday 25 January 2008 06:40, Simon Riggs wrote:
> Notes: As the syntax shows, these would be statement-level triggers
> (only). Requesting row level triggers will cause an error. [As Chris
> Browne explained, if people really want, they can use these facilities
> to create a Before Statement trigger that executes a DELETE, which then
> fires row level calls.]
>
This seems to completly hand-wave away the idea of implementing row level
visibility in statement level triggers, something I am hoping to see
implemented somewhere down the line. Am I missing something?
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] Truncate Triggers
"Robert Treat" <[EMAIL PROTECTED]> writes: > the idea of implementing row level visibility in statement level triggers Huh? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Truncate Triggers
Robert Treat wrote: On Friday 25 January 2008 06:40, Simon Riggs wrote: Notes: As the syntax shows, these would be statement-level triggers (only). Requesting row level triggers will cause an error. [As Chris Browne explained, if people really want, they can use these facilities to create a Before Statement trigger that executes a DELETE, which then fires row level calls.] This seems to completly hand-wave away the idea of implementing row level visibility in statement level triggers, something I am hoping to see implemented somewhere down the line. Am I missing something? The rowset (not row) associated with the statement level trigger would be the whole table in case of a TRUNCATE trigger, so in this (corner) case it's not too helpful. Regards, Andreas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Truncate Triggers
On Sat, Jan 26, 2008 at 04:33:53PM +, Gregory Stark wrote: > "Robert Treat" <[EMAIL PROTECTED]> writes: > > > the idea of implementing row level visibility in statement level triggers > > Huh? I think he means that statement level triggers can see the rows that got affected, presumably by NEW representing a tuplestore. How that would work for BEFORE STATEMENT triggers I don't know. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution > inevitable. > -- John F Kennedy signature.asc Description: Digital signature
Re: [HACKERS] plperl: Documentation on BYTEA decoding is wrong
On Friday 25 January 2008 10:11, Andrew Dunstan wrote:
> Florian Weimer wrote:
> > This doesn't work because '\' is turned into '\\' by PostgreSQL, and
> > not '\134':
> >
> > my $arg = shift;
> > $arg =~ s!\\(\d{3})!chr(oct($1))!ge;
> >
> > Something like this might be better:
> >
> > my $arg = shift;
> > $arg =~ s!\\(?:\\|(\d{3}))!$1 ? chr(oct($1)) : "\\"!ge;
> >
> > You need to do this in one go because pre-escaped backslashes like
> > like '\\101' cause problems otherwise.
> >
> > (All ''-delimited strings in this posting use strict SQL syntax,
> > i.e. no escaped backslashes.)
>
> I think you're right, although that's rather ugly ;-) I'll commit this
> change for now, but if anyone comes up with a simpler recipe I'll be happy.
>
Note we've been using Theo's plperl bytea patch on one of our production
servers for some time; if anyone wants access to that lmk.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(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
[HACKERS] Simple row serialization?
Hi,
I'd like to implement some simple data logging via triggers on a small
number of infrequently updated tables and I'm wondering if there are
some helpful functions, plugins or idioms that would serialize a row
(received for example in a AFTER INSERT trigger) into a string that I'd
store in the log table. There's a limited number of field types
involved: varchars, integers and booleans. I'm not looking for anything
fancy, comma-separated string result will be just fine; Even better,
something like a dictionary ("field_name":"field_value",...) would be
nice. The reason for trying to do it this way is that I don't want to
create separate log tables for every table I wish to log.
signature.asc
Description: OpenPGP digital signature
Re: [HACKERS] Simple row serialization?
Hello,
use plperl.
PostgreSQL hasn't simple tool for it. Maybe this link will be usefull for you
http://www.ciselant.de/projects/pg_ci_diff/
Pavel
On 26/01/2008, Ivan Voras <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'd like to implement some simple data logging via triggers on a small
> number of infrequently updated tables and I'm wondering if there are
> some helpful functions, plugins or idioms that would serialize a row
> (received for example in a AFTER INSERT trigger) into a string that I'd
> store in the log table. There's a limited number of field types
> involved: varchars, integers and booleans. I'm not looking for anything
> fancy, comma-separated string result will be just fine; Even better,
> something like a dictionary ("field_name":"field_value",...) would be
> nice. The reason for trying to do it this way is that I don't want to
> create separate log tables for every table I wish to log.
>
>
>
>
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Simple row serialization?
Ivan Voras wrote:
Hi,
I'd like to implement some simple data logging via triggers on a small
number of infrequently updated tables and I'm wondering if there are
some helpful functions, plugins or idioms that would serialize a row
(received for example in a AFTER INSERT trigger) into a string that
I'd store in the log table. There's a limited number of field types
involved: varchars, integers and booleans. I'm not looking for
anything fancy, comma-separated string result will be just fine; Even
better, something like a dictionary ("field_name":"field_value",...)
would be nice. The reason for trying to do it this way is that I don't
want to create separate log tables for every table I wish to log.
Why not create the audit tables with composite types rather than strings?
create table audit_foo_table as (who text, when timestamptz, old
foo, new foo);
Or you could use an hstore (see contrib).
Or you could possibly use some of the XML support in 8.3 for serialization.
This is a usage question, so it really doesn't belong on -hackers.
cheers
andrew
---(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: [HACKERS] Simple row serialization?
Pavel Stehule wrote: Hello, use plperl. I'd like something more light-weight to reduce complexity of deployment. Something in pgplsql would be ideal. Is there a way to simply iterate over the fields of a row and retrieve field names and values? PostgreSQL hasn't simple tool for it. Maybe this link will be usefull for you http://www.ciselant.de/projects/pg_ci_diff/ Thanks, this is very interesting work! It's an overkill for my current needs but I'll keep it in mind. signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Truncate Triggers
Robert Treat <[EMAIL PROTECTED]> writes: > This seems to completly hand-wave away the idea of implementing row level > visibility in statement level triggers, something I am hoping to see > implemented somewhere down the line. Am I missing something? That was discussed already --- we agreed that TRUNCATE triggers would simply omit that functionality, when and if it's implemented for other trigger types. regards, tom lane ---(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: [HACKERS] Truncate Triggers
Stephen Frost <[EMAIL PROTECTED]> writes: > Of course, the last time this went around the argument was that we > shouldn't add alot of extra code until we actually needed to, while at > the same time we shouldn't use up the few remaining bits we have. The > fact that this makes for an impossible situation seems to have been > lost. No, it hasn't been forgotten at all. Whenever we have to cross that bridge, we'll do so. The questions being asked here are about whether an adequate case has been made for adding *user-visible* complexity, not about nitty little details of internal representation. There are also some compatibility concerns involved. If we add grantable privileges for TRUNCATE and/or DDL operations, then GRANT ALL ON TABLE suddenly conveys a whole lot more privilege than it did before. This could lead to unpleasant surprises in security-sensitive operations. One could also put forward the argument that it's a direct violation of the SQL spec, which after all does specify exactly what privileges ALL is supposed to grant. 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
Re: [HACKERS] Simple row serialization?
Andrew Dunstan wrote: Why not create the audit tables with composite types rather than strings? create table audit_foo_table as (who text, when timestamptz, old foo, new foo); Because this would lead to having a log/shadow/audit table for every table I wish to log. (or is there an opaque "generic row" data type? "record" and "any" generate syntax errors). Or you could use an hstore (see contrib). Doesn't seem applicable. Or you could possibly use some of the XML support in 8.3 for serialization. I need this for 8.1 :) This is a usage question, so it really doesn't belong on -hackers. Thank you - I'm reading the list through gmane and I didn't notice its name "gmane.comp.db.postgresql.devel.general" is incorrect. I'll find a more suitable list. signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Simple row serialization?
Ivan Voras <[EMAIL PROTECTED]> writes: > Andrew Dunstan wrote: >> Or you could possibly use some of the XML support in 8.3 for serialization. > I need this for 8.1 :) There's an even easier way in 8.3: just cast the rowtype to text. regression=# select row(1,2,false)::text; row - (1,2,f) (1 row) Although this won't work at the SQL level in 8.1, I think you might be able to accomplish the equivalent within plpgsql by assigning the rowtype value to a text variable. 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
Re: [HACKERS] Simple row serialization?
On 26/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote:
> Ivan Voras <[EMAIL PROTECTED]> writes:
> > Andrew Dunstan wrote:
> >> Or you could possibly use some of the XML support in 8.3 for serialization.
>
> > I need this for 8.1 :)
>
> There's an even easier way in 8.3: just cast the rowtype to text.
>
> regression=# select row(1,2,false)::text;
>row
> -
> (1,2,f)
> (1 row)
>
> Although this won't work at the SQL level in 8.1, I think you might be
> able to accomplish the equivalent within plpgsql by assigning the
> rowtype value to a text variable.
>
you lost names :(. The best of will be support some like dictionary
so select ((row(1,2,3))::sometype)
{{a:1},{b:2},{c:3}}
Regards
Pavel Stehule
> 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
>
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Simple row serialization?
"Pavel Stehule" <[EMAIL PROTECTED]> writes: > On 26/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote: >> Although this won't work at the SQL level in 8.1, I think you might be >> able to accomplish the equivalent within plpgsql by assigning the >> rowtype value to a text variable. > you lost names :(. And datatype info too; but AFAICT that's exactly what the OP wants. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Truncate Triggers
Stephen Frost <[EMAIL PROTECTED]> writes: > iirc, the suggestion was to exclude the non-SQL-spec things from 'GRANT > ALL' to avoid just that issue. Having to grant TRUNCATE and/or DDL > operation permissions explicitly would be reasonable. This might create > a disconnect with what 'revoke all' does, since that should really > remove all of the perms, but I feel that's reasonable. A 'Default > secure' approach. More like "default impossibly confusing" :-(. "GRANT ALL" doesn't mean grant all privileges? How the heck are you going to explain/justify that to a newbie? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Truncate Triggers
* Tom Lane ([EMAIL PROTECTED]) wrote: > There are also some compatibility concerns involved. If we add > grantable privileges for TRUNCATE and/or DDL operations, then GRANT ALL > ON TABLE suddenly conveys a whole lot more privilege than it did before. > This could lead to unpleasant surprises in security-sensitive > operations. One could also put forward the argument that it's a direct > violation of the SQL spec, which after all does specify exactly what > privileges ALL is supposed to grant. iirc, the suggestion was to exclude the non-SQL-spec things from 'GRANT ALL' to avoid just that issue. Having to grant TRUNCATE and/or DDL operation permissions explicitly would be reasonable. This might create a disconnect with what 'revoke all' does, since that should really remove all of the perms, but I feel that's reasonable. A 'Default secure' approach. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Truncate Triggers
On Sat, 2008-01-26 at 11:19 -0500, Robert Treat wrote: > On Friday 25 January 2008 06:40, Simon Riggs wrote: > > Notes: As the syntax shows, these would be statement-level triggers > > (only). Requesting row level triggers will cause an error. [As Chris > > Browne explained, if people really want, they can use these facilities > > to create a Before Statement trigger that executes a DELETE, which then > > fires row level calls.] > > > > This seems to completly hand-wave away the idea of implementing row level > visibility in statement level triggers, something I am hoping to see > implemented somewhere down the line. Am I missing something? Not sure why you say that. We have a choice: i) TRUNCATE never has access to rows ii) TRUNCATE can have access, in which case it acts like a DELETE Forcing ii) in all cases would effectively negate truncate triggers, so we must have some way of providing both alternatives as options. As Chris explained, if we allow a BEFORE STATEMENT trigger on TRUNCATE to issue a DELETE instead, then we are OK to just allow i) and yet retain the ability to access rows for those that want it. There may be another of way of doing this also, but I'll leave that possibility to whoever tries to implement the feature you mention in the future. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.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
Re: [HACKERS] Simple row serialization?
Tom Lane wrote: "Pavel Stehule" <[EMAIL PROTECTED]> writes: On 26/01/2008, Tom Lane <[EMAIL PROTECTED]> wrote: Although this won't work at the SQL level in 8.1, I think you might be able to accomplish the equivalent within plpgsql by assigning the rowtype value to a text variable. you lost names :(. And datatype info too; but AFAICT that's exactly what the OP wants. Thanks! Having names would be great, but this is sufficient for now. (I've tested it and it works!). signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Simple row serialization?
Ivan, have you seen contrib/hstore ? It's perl-like hash data type and can be ideally suited for you. Oleg On Sat, 26 Jan 2008, Ivan Voras wrote: Andrew Dunstan wrote: Why not create the audit tables with composite types rather than strings? create table audit_foo_table as (who text, when timestamptz, old foo, new foo); Because this would lead to having a log/shadow/audit table for every table I wish to log. (or is there an opaque "generic row" data type? "record" and "any" generate syntax errors). Or you could use an hstore (see contrib). Doesn't seem applicable. Or you could possibly use some of the XML support in 8.3 for serialization. I need this for 8.1 :) This is a usage question, so it really doesn't belong on -hackers. Thank you - I'm reading the list through gmane and I didn't notice its name "gmane.comp.db.postgresql.devel.general" is incorrect. I'll find a more suitable list. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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: [HACKERS] Truncate Triggers
* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > iirc, the suggestion was to exclude the non-SQL-spec things from 'GRANT > > ALL' to avoid just that issue. Having to grant TRUNCATE and/or DDL > > operation permissions explicitly would be reasonable. This might create > > a disconnect with what 'revoke all' does, since that should really > > remove all of the perms, but I feel that's reasonable. A 'Default > > secure' approach. > > More like "default impossibly confusing" :-(. "GRANT ALL" doesn't mean > grant all privileges? How the heck are you going to explain/justify > that to a newbie? "grant all" *already* doesn't mean grant all privileges. This isn't really a change from that. Additionally, there's lots of places where we follow the SQL spec because that's the right thing to do even though it's not always the most intuitive thing to do. I certainly don't feel this is 'impossibly confusing' any more than 'grant all' doesn't mean you can truncate or alter the table today. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Simple row serialization?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Sat, Jan 26, 2008 at 07:32:35PM +0100, Ivan Voras wrote: > Andrew Dunstan wrote: [...] > >Or you could use an hstore (see contrib). > > Doesn't seem applicable. Have a closer look: it might :-) regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFHnBcxBcgs9XrR2kYRAv+zAJwPux4ik4JLOHzwyNanUFNkV2yGwACfdZwl SzA4xZxahgT7d8Z3PyHJwJ4= =5ECG -END PGP SIGNATURE- ---(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
