Re: [SQL] PERSISTANT PREPARE (another point of view)
Milan Oparnica wrote:
Milan Oparnica wrote:
It's simply to complicated to return recordsets through server-side
stored procedures. They are obviously designed to do complex data
manipulation, returning few output variables informing the caller
about final results. Returning records through sets of
user-defined-types is memory and performance waste (please see my
previous post as reply to Steve for more details).
I'm not convinced it's always a win one way or another.
>> Plus it's hard to
maintain and make improvements to such a system. I hate to see 800
user types made for every query we made as stored procedure.
Is this topic completely out of scope in Postgre ?
If I'm missing something too obvious or too important, please let me
know what.
You still haven't said what's "too complicated" about defining a function:
CREATE FUNCTION users_at_dotcom(text) RETURNS SETOF users AS $$
SELECT * FROM users WHERE email LIKE '%@' || $1 || '.com';
$$ LANGUAGE SQL;
SELECT * FROM users_at_dotcom('archonet');
uid | uname | email
-+-+---
1 | richard | [EMAIL PROTECTED]
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Generating table rows from arrays
Is there a convenient way to turn an array into table rows? For
example, I've got an array like {1, 2, 3} and would like to insert
rows:
('aaa', 1)
('aaa', 2)
('aaa', 3)
The first row is constant. I could write a loop with PL/pgsql, I
guess, but I wonder if there are better options nowadays.
--
Florian Weimer<[EMAIL PROTECTED]>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Generating table rows from arrays
Florian Weimer wrote:
Is there a convenient way to turn an array into table rows? For
example, I've got an array like {1, 2, 3} and would like to insert
rows:
('aaa', 1)
('aaa', 2)
('aaa', 3)
The first row is constant. I could write a loop with PL/pgsql, I
guess, but I wonder if there are better options nowadays.
The smallest function I've seen is Merlin Moncure's here:
http://people.planetpostgresql.org/merlin/index.php?/archives/4-fun-with-arrays.html
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Generating table rows from arrays
Hello
se own unpack sql function
http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Array_to_table
Regards
Pavel Stehule
2008/7/17 Florian Weimer <[EMAIL PROTECTED]>:
> Is there a convenient way to turn an array into table rows? For
> example, I've got an array like {1, 2, 3} and would like to insert
> rows:
>
> ('aaa', 1)
> ('aaa', 2)
> ('aaa', 3)
>
> The first row is constant. I could write a loop with PL/pgsql, I
> guess, but I wonder if there are better options nowadays.
>
> --
> Florian Weimer<[EMAIL PROTECTED]>
> BFK edv-consulting GmbH http://www.bfk.de/
> Kriegsstraße 100 tel: +49-721-96201-1
> D-76133 Karlsruhe fax: +49-721-96201-99
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to GROUP results BY month
All, Thanks a million for your help and thoughtful considerations. From this thread I learned lots. As my concrete problem just concerns one year, I used the extract function, but I ve mentally wrote down the date_trunc construct as suggested by Herouth Andreas and Scott, thanks for the tips on indexing Again, thanks a lot. Best, Oliveiros - Original Message - From: "Mark Roberts" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, July 16, 2008 5:29 PM Subject: Re: [SQL] How to GROUP results BY month On Wed, 2008-07-16 at 07:39 +0200, A. Kretschmer wrote: am Tue, dem 15.07.2008, um 18:15:07 -0700 mailte Mark Roberts folgendes: > > On Tue, 2008-07-15 at 14:31 +0200, A. Kretschmer wrote: > > am Tue, dem 15.07.2008, um 13:12:39 +0100 mailte Oliveiros Cristina folgendes: > > > Howdy, all, > > > > > > I have a problem. > > > > > > I have a table which one of the fields is of type date. > > > > > > I need to obtain the totals of the other fields in a by-month basis > > > IS there any easy way to do this using the GROUP BY or any other construct? > > > > ... group by extract(month from date) > > > > > > Andreas > > It's worth noting that extract(month from timestamp) returns a month_no, and thus will not be suitable for grouping queries that span years. Right, but that wasn't the question... Honestly, the way the question was phrased, I'd have assumed that it wanted to group by month (not group by a group of months). Jan 08 is distinct from Jan 07. Please accept my sincerest apologies if you you feel that I misinterpreted the question. I was merely trying to illustrate the difference between what each approach was. -Mark -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Generating table rows from arrays
* Richard Huxton: >> The first row is constant. I could write a loop with PL/pgsql, I >> guess, but I wonder if there are better options nowadays. > > The smallest function I've seen is Merlin Moncure's here: > > http://people.planetpostgresql.org/merlin/index.php?/archives/4-fun-with-arrays.html Ah, neat trick. Thanks. -- Florian Weimer<[EMAIL PROTECTED]> BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_advisory_lock(bigint) vs. LOCK TABLE
Volkan YAZICI wrote: > Hi, > > What's the difference between below two queue implementations? They are two different lock spaces. pg_advisory_lock does not conflict with regular system locks, whereas LOCK TABLE does. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_advisory_lock(bigint) vs. LOCK TABLE
On Thu, 17 Jul 2008, Alvaro Herrera <[EMAIL PROTECTED]> writes: > Volkan YAZICI wrote: >> What's the difference between below two queue implementations? > > They are two different lock spaces. pg_advisory_lock does not conflict > with regular system locks, whereas LOCK TABLE does. Is this documented in somewhere? I couldn't figure out that from the documentation. Regards. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_advisory_lock(bigint) vs. LOCK TABLE
Volkan YAZICI <[EMAIL PROTECTED]> writes: > On Thu, 17 Jul 2008, Alvaro Herrera <[EMAIL PROTECTED]> writes: >> They are two different lock spaces. pg_advisory_lock does not conflict >> with regular system locks, whereas LOCK TABLE does. > Is this documented in somewhere? I couldn't figure out that from the > documentation. Advisory locks are defined as locking application-defined identifiers. Why would you expect them to conflict with system locks, and what would be the relationship exactly? regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
