[GENERAL] Fwd: row_to_json() with numerical indices in stead of associative indices

[Tjibbe]

Hello there,

Is it possible to add an parameter for the function row_to_json()? So
you can choose for:

numerical indices:  [1, true, "string", null]
associative indices:   ["f1":1,"f2": true, "f3":"string","f4": null]

The this extra parameter can save bandwidth between the
webserver(postgresql/php) and client(javascript).

Now i'm doing it like this:

CREATE FUNCTION to_num_json(_in json) RETURNS json AS $$
DECLARE
_pair RECORD;
_arr json[];
BEGIN
   FOR _pair IN SELECT * FROM json_each(_in) LOOP
  _arr := _arr || _pair.value;
   END LOOP;
   RETURN to_json(_arr);
END
$$ language plpgsql;

SELECT to_num_json(
row_to_json((5, TRUE, 'string', NULL))
);

greetings

Tjibbe

Re: [GENERAL] [HACKERS] Trust intermediate CA for client certificates

[Bruce Momjian]

On Thu, Mar 21, 2013 at 01:42:55PM +0800, Craig Ringer wrote:
> On 03/19/2013 09:46 PM, Stephen Frost wrote:
> > * Craig Ringer (cr...@2ndquadrant.com) wrote:
> >> As far as I'm concerned that's the immediate problem fixed. It may be
> >> worth adding a warning on startup if we find non-self-signed certs in
> >> root.crt too, something like 'WARNING: Intermediate certificate found in
> >> root.crt. This does not do what you expect and your configuration may be
> >> insecure; see the Client Certificates chapter in the documentation.'
> >
> > I'm not sure that I follow this logic, unless you're proposing that
> > intermediate CAs only be allowed to be picked up from system-wide
> > configuration? That strikes me as overly constrained as I imagine there
> > are valid configurations today which have intermediate CAs listed, with
> > the intention that they be available for PG to build the chain from a
> > client cert that is presented back up to the root. Now, the client
> > might be able to provide such an intermediate CA cert too (one of the
> > fun things about SSL is that the client can send any 'missing' certs to
> > the server, if it has them available..), but it also might not.
> >
> 
> Drat, you're quite right. I've always included the full certificate
> chain in client certs but it's in no way required.
> 
> I guess that pretty much means mainaining the status quo and documenting
> it better.

I have developed the attached patch to document this behavior.  My goals
were:

* clarify that a cert can match a remote intermediate or root certificate
* clarify that the client cert must match a server root.crt
* clarify that the server cert much match a client root.crt
* clarify that the root certificate does not have to be specified
  in the client or server cert as long as the remote end has the chain
  to the root

Does it meet these goals?  Is it correct?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
new file mode 100644
index 955f248..ad54564
*** a/doc/src/sgml/libpq.sgml
--- b/doc/src/sgml/libpq.sgml
*** ldap://ldap.acme.com/cn=dbserver,cn=host
*** 7179,7193 
 intermediate certificate authority, rather than one that is
 directly trusted by the server.  To use such a certificate, append the
 certificate of the signing authority to the postgresql.crt
!file, then its parent authority's certificate, and so on up to a
!root authority that is trusted by the server.  The root
!certificate should be included in every case where
!postgresql.crt contains more than one certificate.

  

!Note that root.crt lists the top-level CAs that are
!considered trusted for signing server certificates.  In principle it need
 not list the CA that signed the client's certificate, though in most cases
 that CA would also be trusted for server certificates.

--- 7179,7193 
 intermediate certificate authority, rather than one that is
 directly trusted by the server.  To use such a certificate, append the
 certificate of the signing authority to the postgresql.crt
!file, then its parent authority's certificate, and so on up to a certificate
!authority, root or intermediate, that is trusted by
!the server, i.e. appears in the server's root.crt
!file.

  

!Note that the client's ~/.postgresql/root.crt lists the top-level CAs 
!that are considered trusted for signing server certificates.  In principle it need
 not list the CA that signed the client's certificate, though in most cases
 that CA would also be trusted for server certificates.

diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
new file mode 100644
index ab51782..fbed06f
*** a/doc/src/sgml/runtime.sgml
--- b/doc/src/sgml/runtime.sgml
*** pg_dumpall -p 5432 | psql -d postgres -p
*** 1986,1995 
 intermediate certificate authority, rather than one that is
 directly trusted by clients.  To use such a certificate, append the
 certificate of the signing authority to the server.crt file,
!then its parent authority's certificate, and so on up to a root
!authority that is trusted by the clients.  The root certificate should
!be included in every case where server.crt contains more than
!one certificate.

  

--- 1986,1995 
 intermediate certificate authority, rather than one that is
 directly trusted by clients.  To use such a certificate, append the
 certificate of the signing authority to the server.crt file,
!then its parent authority's certificate, and so on up to a certificate
!authority, root or intermediate, that is trusted by
!clients, i.e. appears in the clients' root.crt
!files.

  


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes 

[GENERAL] Full Stored Procedure Support, any time soon ?

[Noel Diaz]

Hi,

My firm is considering a very large move from PAID RDBMS to Postgresql. 
The lack of full support for Stored Procedures is currently a deal breaker.

We use many features, like:
- output parameters
- Callable statements to call the procs
- Multiple result sets
- nested procedure calls

Is there in the *near* future the possibility of adding support for these?

Simple functions are _not_ enough. EnterpriseDB seems to have such support but 
it is a payed for feature.

Any help will be greatly appreciated.

Many thanks

Noeldr 

Re: [GENERAL] Full Stored Procedure Support, any time soon ?

[John R Pierce]

On 11/30/2013 12:17 PM, Noel Diaz wrote:


My firm is considering a very large move from PAID RDBMS to Postgresql.
The lack of full support for Stored Procedures is currently a deal 
breaker.


We use many features, like:
- output parameters
- Callable statements to call the procs
- Multiple result sets
- nested procedure calls

Is there in the *near* future the possibility of adding support for these?


we're doing much the same, moving from that big O to Postgres,  but we 
opted to STOP using stored procedures and instead reimplement our core 
business logic as a middleware service (which happens to be in Java but 
really could be in most any language that has decent database and 
messaging and/or RPC support).   we find its a lot easier to debug the 
middleware than complex problems buried deep in stored procedures.We 
still use SOME postgres functions, for performance optimization, 
triggers (for partitioning) and so forth, but we don't have the bulk of 
our business logic there.the client applications are not allowed ANY 
database access, they have to go through the middleware.




--
john r pierce  37N 122W
somewhere on the middle of the left coast

Re: [GENERAL] Full Stored Procedure Support, any time soon ?

[Alban Hertroys]

On 30 Nov 2013, at 21:17, Noel Diaz  wrote:

> Hi,

Welcome to the list!

> My firm is considering a very large move from PAID RDBMS to Postgresql. 
> The lack of full support for Stored Procedures is currently a deal breaker.

We’re not intimately familiar with your paid RDBMS, so perhaps you could 
elaborate on what _exactly_ you’re missing? I’m going to wager a guess to what 
you mean though, hopefully that helps clear things up.

As far as people here are concerned, procedures are merely functions that don’t 
return anything. We have those.

> We use many features, like:
> - output parameters

Functions have IN, OUT, INOUT and VARIADIC parameters. They also have return 
parameters. How does that not cover what you require?

http://www.postgresql.org/docs/9.2/static/sql-createfunction.html

> - Callable statements to call the procs

Not sure what you mean here. Obviously functions can be called, otherwise what 
would be their point?

You can use either select my_func() for functions that return a single value or 
select … from my_func() for functions that return result sets.

> - Multiple result sets

Since you’re talking about procedures, you can’t possibly mean that those 
return multiple result sets?

Functions can handle multiple result sets internally. You can loop through as 
many result sets in your function as you like.

If the functions return a refcursor or a set of records, they can even _return_ 
multiple result sets, but you’ll have to cast those function result rows to a 
meaningful type before being able to use them.

> - nested procedure calls

Function calls can be nested, they even support recursion.

> Simple functions are _not_ enough.

Why not? What don’t they do that you require?


Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Full Stored Procedure Support, any time soon ?

[Noel Diaz]

Alban, 

Thank you very much for the Link.

Let me see if I can explain from the beginning:

I see that output parameters are possible in functions as well as return 
values!! So that is a plus!

When I mentioned "Callable Statements" I was referring to the way we call the 
procs from java : http://jdbc.postgresql.org/documentation/91/callproc.html

I was not sure that "cstm.prepareCall" was allowed with functions (all examples 
I saw used preparedStatements) but after further digging further it seems 
possible as the link above indicates. This is another plus! 

When I mentioned "multiple result sets",  That is EXACTLY what it means 
You can create a procedure that does something like this:

SELECT * FROM customers WHERE ... ; /* any complex query */
SELECT * FROM orders  ;   /* any other set that DOES NOT needs to 
be joined to the above*/
 And so on                                 /* Sometimes this is useful 
because intermediate results do not need to be sent back to the clients etc */ 

With that we can, in ONE round trip,  process data and we use the 
cstmt.getMoreResults  / cstmt.getResultSet Pattern like so: 
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.apdv.java.doc%2Fdoc%2Ftjvjdmlt.htm

Hopefully I shed some light in my incomplete description

Thank you for the prompt reply,

Noeldr



On Saturday, November 30, 2013 4:33 PM, Alban Hertroys  
wrote:
 
On 30 Nov 2013, at 21:17, Noel Diaz  wrote:

> Hi,

Welcome to the list!

> My firm is considering a very large move from PAID RDBMS to Postgresql. 
> The lack of full support for Stored Procedures is currently a deal breaker.

We’re not intimately familiar with your paid RDBMS, so perhaps you could 
elaborate on what _exactly_ you’re missing? I’m going to wager a guess to what 
you mean though, hopefully that helps clear things up.

As far as people here are concerned, procedures are merely functions that don’t 
return anything. We have those.

> We use many features, like:
> - output parameters

Functions have IN, OUT, INOUT and VARIADIC parameters. They also have return 
parameters. How does that not cover what you require?

http://www.postgresql.org/docs/9.2/static/sql-createfunction.html

> - Callable statements to call the procs

Not sure what you mean here. Obviously functions can be called, otherwise what 
would be their point?

You can use either select my_func() for functions that return a single value or 
select … from my_func() for functions that return result sets.

> - Multiple result sets

Since you’re talking about procedures, you can’t possibly mean that those 
return multiple result sets?

Functions can handle multiple result sets internally. You can loop through as 
many result sets in your function as you like.

If the functions return a refcursor or a set of records, they can even _return_ 
multiple result sets, but you’ll have to cast those function result rows to a 
meaningful type before being able to use them.

> - nested procedure calls

Function calls can be nested, they even support recursion.


> Simple functions are _not_ enough.

Why not? What don’t they do that you require?


Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: [GENERAL] Full Stored Procedure Support, any time soon ?

[Thomas Kellerer]

Alban Hertroys wrote on 30.11.2013 22:34:

- Multiple result sets


Since you’re talking about procedures, you can’t possibly mean that those 
return multiple result sets?


Yes, basically something like this:

create procedure foobar()
begin
  select * from table_1;
  select * from table_2
end;

I know that at least SQL Server and MySQL can do that. Maybe others as well 
(Sybase most probably).

But I always failed do see the actual advantage of that because the results can't be 
"used" any further (e.g. in a JOIN or a subselect).
I also don't understand why having a single procedure doing a lot of stuff is 
better than having several procedures (or functions) doing one defined thing.

From a JDBC point of view the code simply asks the Statement whether it has any 
more result sets
and loops over this until all results are returned.

Thomas



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Full Stored Procedure Support, any time soon ?

[Noel Diaz]

Thomas, RIGHT ON!

Sometimes I have seen this feature used to hide multiple round trips and 
intermediate result processing from the clients
Some others is simply because the client is generating a predefined data 
structure that has little resemblance to the schema and this "feature" becomes 
convenient
Some others is "ad-hoc" tools that allow to see multiple datasets in the UI, 
etc...

Anyway I just saw this in the TODO list: (http://wiki.postgresql.org/wiki/Todo) 
:

_ Implement stored procedures
This might involve the control of transaction state and the return of multiple 
result sets
* PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
* Proposal: real procedures again (8.4)
* http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php
* Gathering specs and discussion on feature (post 9.1)

Does anybody know if any the committers are working on that list?



On Saturday, November 30, 2013 5:21 PM, Thomas Kellerer  
wrote:
 
Alban Hertroys wrote on 30.11.2013 22:34:

>> - Multiple result sets
>
> Since you’re talking about procedures, you can’t possibly mean that those 
> return multiple result sets?

Yes, basically something like this:

create procedure foobar()
begin
   select * from table_1;
   select * from table_2
end;

I know that at least SQL Server and MySQL can do that. Maybe others as well 
(Sybase most probably).

But I always failed do see the actual advantage of that because the results 
can't be "used" any further (e.g. in a JOIN or a subselect).
I also don't understand why having a single procedure doing a lot of stuff is 
better than having several procedures (or functions) doing one defined thing.

From a JDBC point of view the code simply asks the Statement whether it has any 
more result sets
and loops over this until all results are returned.

Thomas



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Full Stored Procedure Support, any time soon ?

[John R Pierce]

On 11/30/2013 2:30 PM, Noel Diaz wrote:

This might involve the control of transaction state and the return
of multiple result sets

  * PL/pgSQL stored procedure returning multiple result sets
(SELECTs)?

  * Proposal: real procedures again (8.4)

  * http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php
  * Gathering specs and discussion on feature (post 9.1)


Does anybody know if any the committers are working on that list?


I don't think the libpq API will allow that first one, a SQL query can 
only return a single recordset of rows that all have the same fields.




--
john r pierce  37N 122W
somewhere on the middle of the left coast

Re: [GENERAL] Full Stored Procedure Support, any time soon ?

[Thomas Kellerer]

John R Pierce wrote on 30.11.2013 23:47:

On 11/30/2013 2:30 PM, Noel Diaz wrote:

This might involve the control of transaction state and the return of 
multiple result sets

  * PL/pgSQL stored procedure returning multiple result sets (SELECTs)? 

  * Proposal: real procedures again (8.4) 

  * http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php
  * Gathering specs and discussion on feature (post 9.1) 


Does anybody know if any the committers are working on that list?


I don't think the libpq API will allow that first one, a SQL query can only 
return a single recordset
of rows that all have the same fields.


I don't think that's a limitation. The client will still iterate through one 
result set at a time.

The "potential" result sets would need to be somehow "prepared to be sent" on 
the server. When the client has exhausted one result set, it requests the next one (or asks if 
there _is_ a next one). Only then the server starts streaming the results. That way libpg would 
only deal with a single result at a time.

I don't think SQL Server or MySQL actually send all results at once (definitely 
not when using JDBC)





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Use environment variables in postgresql.conf

[imagene...@gmail.com]

Is it possible to use environment variables in postgresql.conf and perhaps
related configuration files?

For example, I would like to use an env variable to specify the data
directory.

Re: [GENERAL] Full Stored Procedure Support, any time soon ?

[John R Pierce]

On 11/30/2013 3:27 PM, Thomas Kellerer wrote:
I don't think that's a limitation. The client will still iterate 
through one result set at a time. 


PQexec etc return a  PGresult *

the various PQx functions that accept PGresult * as an argument, 
each only return info about the single resultset, like PQntuples, 
PQnfields, PQgetvalue, PQfname, PQfnumber,  etc...   I guess we'd need 
some new functions to return the number of resultsets, and choose a 
resultset by index number, then those above functions would refer to the 
'currently chosen' PQresult ?   or at least a res = 
PQnextresult(PGresult *res), which returns 0 if there is no next resultset ?










--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Use environment variables in postgresql.conf

[John R Pierce]

On 11/30/2013 3:54 PM, imagene...@gmail.com wrote:
Is it possible to use environment variables in postgresql.conf and 
perhaps related configuration files?


For example, I would like to use an env variable to specify the data 
directory. 


that specific case is generally handled not via postgresql.conf, but 
rather by the arguments passed pg_ctl in the service 
start/stop/restart/reload scripts.  on my RHEL platforms, its in turn 
read from /etc/sysconfig/pgsql/$0 (where $0 is the service script name), 
which in fact sets environment variables used by said service script.





--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Use environment variables in postgresql.conf

[Andrew Sullivan]

On Sat, Nov 30, 2013 at 06:54:31PM -0500, imagene...@gmail.com wrote:
> Is it possible to use environment variables in postgresql.conf and perhaps
> related configuration files?
> 
> For example, I would like to use an env variable to specify the data
> directory.

On top of what John Pierce says in the thread, I would very strongly
urge you not to do this.  It makes troubleshooting on failure
notoriously hard.  Use the mechanisms the start-up files do, I
suggest.  

That said, see section 18 of the manual -- in particular, 18.1.3 in
the 9.3 manual ("Other ways to set parameters").  I recommend against
all that on the basis of some somewhat painful experience, but you can
do it that way.

If you're determined to do it, I _strongly_ suggest using several
different postgres.conf files and then only using the config_file
parameter to control this.  At least then you can look at what the
settings were when you have a crashed or otherwise troublesome system.

Best,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Use environment variables in postgresql.conf

[imagene...@gmail.com]

The questions are:

1. Has var expansion in configuration files been contemplated?
2. Why not do it?

So the presumed workflow is that:

1. There is no support for environment variable expansion in configuration
files
2. Use of environment variable values should only be passed to the
"postgres" on the command line
3. Ie thus passed through to pg_ctl from the OS init scripts.

Reasons why it's perhaps useful to change the presumed workflow:

1. It's perhaps inconvenient
2. Variables are a fundamental concept for configuration
3. Moving configuration to os specific scripts defies the DRY (don't repeat
yourself) paradigm

Proposed workflow:
1. Environment initialization, meaning the declaration of environment
variables (in the sense that "env -i" is probably spawned in the OS
specific scripts and is thus quite empty) for "pg_ctl" should be done in a
postgresql specific shell file.
2. Variable expansion should be done in postgresql specific configuration
files.

On the other hand:
1. One could just generate the conf files
2. Assign env vars to absolute paths/symbolic links

Thanks for your reply Andrew, however I do not necessarily wish to conform
to arbitrary expectations forced by the current implementation if it is
inconvenient/incomplete. Please evaluate the value of workflows facilitated
by said modifications. Using the OS specific start up scripts for
configuration of contexts they are intended to initiate is fundamentally
incorrect. As specified, I do not like the declaration of "postgres"
configuration as command-line options or as environment variables because
said declarations invariably move away from the centrailization of
postgresql configuration to the initailization scripts. However,
considering the PGOPTIONS environment variable, I think it's initailization
in a standardized way by the init scripts is the most relevant to this
workflow.


On Sat, Nov 30, 2013 at 8:06 PM, Andrew Sullivan wrote:

> On Sat, Nov 30, 2013 at 06:54:31PM -0500, imagene...@gmail.com wrote:
> > Is it possible to use environment variables in postgresql.conf and
> perhaps
> > related configuration files?
> >
> > For example, I would like to use an env variable to specify the data
> > directory.
>
> On top of what John Pierce says in the thread, I would very strongly
> urge you not to do this.  It makes troubleshooting on failure
> notoriously hard.  Use the mechanisms the start-up files do, I
> suggest.
>
> That said, see section 18 of the manual -- in particular, 18.1.3 in
> the 9.3 manual ("Other ways to set parameters").  I recommend against
> all that on the basis of some somewhat painful experience, but you can
> do it that way.
>
> If you're determined to do it, I _strongly_ suggest using several
> different postgres.conf files and then only using the config_file
> parameter to control this.  At least then you can look at what the
> settings were when you have a crashed or otherwise troublesome system.
>
> Best,
>
> A
>
> --
> Andrew Sullivan
> a...@crankycanuck.ca
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Use environment variables in postgresql.conf

[David Johnston]

imagene...@gmail.com wrote
> The questions are:
> 
> 1. Has var expansion in configuration files been contemplated?
> 2. Why not do it?

1. Probably
2. More important things to focus on; distros/installers all have their own
idea of what is "best" so the decisions have been left to these OS-specific
entities.


> Reasons why it's perhaps useful to change the presumed workflow:
> 
> 1. It's perhaps inconvenient
> 2. Variables are a fundamental concept for configuration
> 3. Moving configuration to os specific scripts defies the DRY (don't
> repeat
> yourself) paradigm

1. Not enough for someone to propose a working patch apparently
2. OK
3. Possibly...and see my response to #2 above


> Proposed workflow:
> 1. Environment initialization, meaning the declaration of environment
> variables (in the sense that "env -i" is probably spawned in the OS
> specific scripts and is thus quite empty) for "pg_ctl" should be done in a
> postgresql specific shell file.
> 2. Variable expansion should be done in postgresql specific configuration
> files.

You seem to only care about Linux...and this workflow seem woefully
incomplete.  If you have something specific in mind please share.


> On the other hand:
> 1. One could just generate the conf files
> 2. Assign env vars to absolute paths/symbolic links

1. Yes, this seems to be a useful approach.
2. Hey, if it works for you.


> Thanks for your reply Andrew, however I do not necessarily wish to conform
> to arbitrary expectations forced by the current implementation if it is
> inconvenient/incomplete. Please evaluate the value of workflows
> facilitated
> by said modifications. Using the OS specific start up scripts for
> configuration of contexts they are intended to initiate is fundamentally
> incorrect. 

To be fair the current implementation has probably been around for the past
10 years - well before current DevOps thinking, Puppet/Chef, etc... came
around.  It seems to work for the vast majority of cases, across numerous
OSs, and different distros have indeed done some tweaking to make things
easier for their platforms.

PostgreSQL itself can provide a more flexible and manageable installation
platform if the need can be sold to the current group of -hackers or if
someone with enough motivation comes in with a well-designed proposal.  
Changing core in this way, though, might take 2-3 years.  Or that someone
takes what is currently in place and creates a friendly wrapper on top of it
- just like distros do currently - and releases/uses it whenever it is
ready.

So, in summary, it is better to contribute than to conform.

David J.







--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Use-environment-variables-in-postgresql-conf-tp5781027p5781037.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] unnest on multi-dimensional arrays

[Zev Benjamin]

It appears that unnest, when called on a multi-dimensional array, 
effectively flattens the array first.  For example:


=> select * from unnest(array[array[1, 2], array[2, 3]]);
 unnest

  1
  2
  2
  3
(4 rows)

while I would have expect something like the following:

=> select * from unnest(array[array[1, 2], array[2, 3]]);
 unnest

   {1, 2}
   {2, 3}
(2 rows)

Is there any way to get the latter behavior?


Zev


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Full Stored Procedure Support, any time soon ?

[David Johnston]

Noel Diaz wrote
> T_ Implement stored procedures
> This might involve the control of transaction state and the return of
> multiple result sets
>   * PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
>   * Proposal: real procedures again (8.4)
>   * http://archives.postgresql.org/pgsql-hackers/2010-09/msg00542.php
>   * Gathering specs and discussion on feature (post 9.1)
> 
> Does anybody know if any the committers are working on that list?

I have not seen any recent discussions on the list pertaining to
implementing callable stored procedures.  Thus nothing like this has a
chance to make it out with the next release 6+ months from now.  The
earliest possible implementation would be 1.5 years from now and that is
only if someone decides to dedicate the next year solely on this feature.

Some "foundation" work has been ongoing recently, notably "background
workers", which is how these things are likely to play out.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Full-Stored-Procedure-Support-any-time-soon-tp5780999p5781040.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Fwd: row_to_json() with numerical indices in stead of associative indices

[Michael Paquier]

On Sat, Nov 30, 2013 at 11:48 PM, Tjibbe  wrote:
> Hello there,
>
> Is it possible to add an parameter for the function row_to_json()? So
> you can choose for:
>
> numerical indices:  [1, true, "string", null]
> associative indices:   ["f1":1,"f2": true, "f3":"string","f4": null]
>
> The this extra parameter can save bandwidth between the
> webserver(postgresql/php) and client(javascript).
>
> Now i'm doing it like this:
>
> CREATE FUNCTION to_num_json(_in json) RETURNS json AS $$
> DECLARE
> _pair RECORD;
> _arr json[];
> BEGIN
>FOR _pair IN SELECT * FROM json_each(_in) LOOP
>   _arr := _arr || _pair.value;
>END LOOP;
>RETURN to_json(_arr);
> END
> $$ language plpgsql;
>
> SELECT to_num_json(
> row_to_json((5, TRUE, 'string', NULL))
> );
You could rename your function to_num_json to row_to_json and trick
search_path to include it in your client depending on the schema where
the new function is as two functions with the same name but different
arguments can live together. IMO, you are right to do that with
plpgsql and json_each.

Regards,
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general