Hello,
I have a simple table that has a trigger to set a last_modified column
using the following:
CREATE OR REPLACE FUNCTION set_last_modified ()
RETURNS TRIGGER
AS $$
BEGIN
NEW.last_modified = NOW();
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER
On Thu, 6 Aug 2009 13:15:57 -0400
Merlin Moncure mmonc...@gmail.com wrote:
CREATE OR REPLACE FUNCTION set_last_modified ()
RETURNS TRIGGER
AS $$
BEGIN
IF NEW != OLD THEN -- 8.4 syntax
NEW.last_modified = NOW();
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;
Thanks - I'll
On Thu, 6 Aug 2009 13:15:57 -0400
Merlin Moncure mmonc...@gmail.com wrote:
CREATE OR REPLACE FUNCTION set_last_modified ()
RETURNS TRIGGER
AS $$
BEGIN
IF NEW != OLD THEN -- 8.4 syntax
NEW.last_modified = NOW();
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;
Interestingly,
On Thu, 6 Aug 2009 16:58:02 -0400
Michael Glaesemann g...@seespotcode.net wrote:
That's not a SELECT query per se: AIUI it's how the evaluation of
the NEW != OLD expression is evaluated within the PL/pgSQL function
as part of the IF statement (note the line 2 at IF context line).
It's just
I've been asked to put together a list of reasons to upgrade a db
from 8.1 to 8.3 and I've looked over the changelog, but they want a
bullet list of 4-5 top things. I'm curious what others would say the
most 5 important updates from 8.1 to 8.3 are.
I can say performance improvevents but I'm not
Thanks for all the replies, I'm picking this one to reply to. Winner!
On Fri, 24 Apr 2009 13:40:29 -0400
Brad Nicholson bnich...@ca.afilias.info wrote:
On Fri, 2009-04-24 at 12:01 -0500, Josh Trutwin wrote:
I've been asked to put together a list of reasons to upgrade a db
from 8.1 to 8.3
On Tue, 10 Mar 2009 12:37:21 +
Gregory Stark st...@enterprisedb.com wrote:
Searching on google it seems this is a typical error message when
you have a hand-compiled gmp installed locally in /usr/local/lib as
well as a system gmp installed in /usr/lib. So you may be getting
one version of
Hi - I'm trying to build 8.3.6 on a box recently upgraded from Sarge
to Lenny and I get the following error during compile:
make[4]: Entering directory
`/backup/source/db/postgresql-8.3.6/src/backend/utils/adt' gcc -O2
-Wall -Wmissing-prototypes -Wpointer-arith -Winline
On Tue, 10 Mar 2009 00:49:32 -0400
Tom Lane t...@sss.pgh.pa.us wrote:
Josh Trutwin j...@trutwins.homeip.net writes:
snip
geo_ops.c /usr/lib/gcc/i486-linux-gnu/4.3.2/cc1: symbol lookup
error: /usr/lib/libmpfr.so.1: undefined symbol:
__gmp_get_memory_functions
[ blink... ] There's
On Mon, 9 Mar 2009 22:01:38 -0700 (MST)
Leonel Nunez lis...@enelserver.com wrote:
Lenny has 8.3.6 why don't just apt-get install postgresql ???
http://packages.debian.org/lenny/postgresql
If I were doing a complete reinstall I would definitely go that
route. I came from a slackware
On Fri, 27 Feb 2009 09:34:08 +
Richard Huxton d...@archonet.com wrote:
CREATE TRIGGER prevent_empty_updates BEFORE UPDATE ON test FOR
EACH ROW EXECUTE PROCEDURE prevent_empty_updates();
Actually after writing this, this TOO does not seem to work after
an ADD COLUMN. :/ Any
I found the following on a blog post
(http://www.depesz.com/index.php/2007/09/08/avoiding-empty-updates/)
which had a rule to prevent empty updates:
CREATE RULE no_unchanging_updates AS
ON UPDATE
TO test_table
WHERE ROW(OLD.*) IS NOT DISTINCT FROM ROW(NEW.*)
DO INSTEAD NOTHING;
Works great, but
On Tue, 22 Jul 2008 05:36:39 -0500 (CDT)
Brandon Metcalf [EMAIL PROTECTED] wrote:
I've been able to find a couple of packages, but wondering if there
is a good system out there what will create an ER diagram of an
existing PostgreSQL DB. Open source would be nice.
For reverse engineering
On Thu, 17 Apr 2008 11:29:56 -0700
Joshua D. Drake [EMAIL PROTECTED] wrote:
Hello,
I am currently chilling at MySQLCon. If any other Elephant riders
who are doing a little Dolphin hunting are about... I am in
Ballroom E about to give a talk on what Mysql can learn from
PostgreSQL.
Is your
Hi,
I have an upgrade script that is supposed to install items into a
postgresql database if they don't already exist. One of the items I'm
having a hard time with is aggregates. I want to check if aggregate
foo doesn't exist, then run an SQL command to generate it.
if
On Mon, 24 Mar 2008 14:02:02 -0500
Erik Jones [EMAIL PROTECTED] wrote:
On Mar 24, 2008, at 1:09 PM, Martijn van Oosterhout wrote:
On Mon, Mar 24, 2008 at 12:47:43PM -0500, Josh Trutwin wrote:
My code to check if an aggregate exists runs this query:
SELECT * FROM pg_catalog.pg_aggretate
Hi,
I have a column in a table defined as type TEXT and I'd like to
convert to a TIME type - I tried this:
ALTER TABLE t1 ALTER COLUMN tcol TYPE TIME WITHOUT TIME ZONE;
which throws:
ERROR: column tcol cannot be cast to type pg_catalog.time
From reading the ALTER TABLE documents there
On Mon, 03 Mar 2008 10:42:50 -0700
dmp [EMAIL PROTECTED] wrote:
Hello,
The project MyJSQLView will provided basic support
for array types in PostgreSQL at the next release.
Information is desired from anyone that uses arrays
in PostgreSQL to effect this support. Just a couple
of questions.
On Mon, 3 Mar 2008 20:48:55 +0100
Karsten Hilbert [EMAIL PROTECTED] wrote:
On Mon, Mar 03, 2008 at 01:22:17PM -0600, Erik Jones wrote:
Where are you getting this information.
IMO the OP wanted to know how people *use* arrays, not how
one *can* use arrays.
That was my thought, sort of a
On Wed, 30 Jan 2008 13:20:58 -0500
Tom Hart [EMAIL PROTECTED] wrote:
I have 4 years of mySQL experience (I know, I'm sorry)
Why is this something to apologize for? I used to use MySQL for
everything and now use PostgreSQL for the majority of my DB needs. I
certainly advocate PG now to anyone
On Thu, 29 Nov 2007 17:08:41 +1100
Chris Velevitch [EMAIL PROTECTED] wrote:
I'm currently using 7.4 and I trying find out what the
value/advantage of upgrading to a more recent version and to which
version.
The release notes - esp. for the major versions - are the best source
of finding out
On Postgresql 8.1 I am guessing there isn't a convenient way to alter
a trigger to change its before/after behavior? I wrote one of my
first triggers using an AFTER and now I release I needed to
do BEFORE. It's used on a couple tables so I was hoping to avoid
dropping it and re-creating it but
On Mon, 29 Oct 2007 22:33:28 +
Richard Huxton [EMAIL PROTECTED] wrote:
Josh Trutwin wrote:
On Postgresql 8.1 I am guessing there isn't a convenient way to
alter a trigger to change its before/after behavior? I wrote one
of my first triggers using an AFTER and now I release I needed
I'm going to be using a smarty plugin to paginate some result sets
for display in smarty templates. I was reading that using LIMIT/OFFSET
generates multiple query plans so I'm curious if it would be better
to do a:
SELECT * FROM table WHERE foo=bar ORDER BY abc LIMIT x OFFSET y;
or just:
SELECT
On Fri, 19 Oct 2007 18:19:55 -0500
Michael Glaesemann [EMAIL PROTECTED] wrote:
On Oct 19, 2007, at 16:03 , Josh Trutwin wrote:
SELECT * FROM table WHERE foo=bar ORDER BY abc LIMIT x OFFSET y;
The server will have to generate at most OFFSET + LIMIT rows,
returning LIMIT rows or fewer
Hi,
Is it possible to find the intersection of two array values?
a = '{1,2,3}'
b = '{2,3,4}'
a intersect b = '{2,3}'
Assume I need to write a pl/pgsql function to do this.
Josh
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
On Wed, 17 Oct 2007 10:19:43 -0500
Josh Trutwin [EMAIL PROTECTED] wrote:
Hi,
Is it possible to find the intersection of two array values?
a = '{1,2,3}'
b = '{2,3,4}'
a intersect b = '{2,3}'
Assume I need to write a pl/pgsql function to do this.
nm - I just wrote a function - though
On Wed, 17 Oct 2007 11:12:27 -0500
Rodrigo De León [EMAIL PROTECTED] wrote:
On 10/17/07, Josh Trutwin [EMAIL PROTECTED] wrote:
nm - I just wrote a function - though curious if this is the most
effecient way:
If you only want TRUE or FALSE, you can use '':
t=# SELECT '{1,2}'::INT
This is only going to work for one-dimensional arrays (I'm not sure
how you would ever fix that with the support postgres has for
arrays) but the (computational) complexity of having an embedded
FOR loops looks bad for performance. As you can already use '=ANY'
syntax to search inside an
On Wed, 17 Oct 2007 12:33:13 -0400
Merlin Moncure [EMAIL PROTECTED] wrote:
On 10/17/07, Josh Trutwin [EMAIL PROTECTED] wrote:
On Wed, 17 Oct 2007 11:12:27 -0500
Rodrigo De León [EMAIL PROTECTED] wrote:
On 10/17/07, Josh Trutwin [EMAIL PROTECTED] wrote:
nm - I just wrote a function
On Wed, 17 Oct 2007 17:08:06 +0100
Sam Mason [EMAIL PROTECTED] wrote:
CREATE OR REPLACE FUNCTION array_intersect (array1
INTEGER[],array2 INTEGER[]) RETURNS INTEGER[] AS $$
DECLARE
out INTEGER[];
BEGIN
IF array1 IS NULL OR array2 IS NULL THEN
RETURN
On Wed, 17 Oct 2007 17:49:01 +0100
Sam Mason [EMAIL PROTECTED] wrote:
On Wed, Oct 17, 2007 at 11:31:51AM -0500, Josh Trutwin wrote:
Is the =ANY specific to PG 8.2 or higher? On 8.1.10:
It appears (according to [1] and [2]) that you may be able to just
remove the '=' to get it working
On Wed, 17 Oct 2007 17:42:21 +0100
Sam Mason [EMAIL PROTECTED] wrote:
snip
CREATE OR REPLACE FUNCTION array_intersect (array1
INTEGER[],array2 INTEGER[]) RETURNS INTEGER[] AS $$
DECLARE
out INTEGER[];
BEGIN
out := '{}'::INTEGER[];
IF array1 IS NULL OR
On Wed, 17 Oct 2007 10:04:21 -0700
David Fetter [EMAIL PROTECTED] wrote:
snip
CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT $1[i] AS the_intersection
FROM generate_series(
array_lower($1,1),
On Wed, 17 Oct 2007 11:26:05 -0700
David Fetter [EMAIL PROTECTED] wrote:
Doesn't appear to work on 8.1:
psql= select array_intersect('{1,2,3}', '{2,3,4}');
ERROR: could not determine anyarray/anyelement type because input
has type unknown
As mentioned in the release notes ;), it's
Is it possible to somehow query the system catalog to find out which
clusters/databases have a certain schema?
When running the query:
SELECT * FROM pg_catalog.pg_namespace WHERE nspname = 'myschema';
It always only finds data for the current session, not all clusters,
even when connected as
I was following the posts from a month or two ago about reordering
columns using syntax similar to MySQL's:
ALTER TABLE tbl ALTER COL col1 AFTER/BEFORE col2;
I have been working on a new project that adds some functionality to
an existing database schema, which caused some tables to expand with
If I create an ON UPDATE trigger run on each row after update, does
the trigger fire only on rows affected by the update or for all rows?
For example:
CREATE TRIGGER my_update_trigger
AFTER UPDATE ON my_table
FOR EACH ROW
EXECUTE PROCEDURE my_update_proc;
UPDATE my_table SET my_val =
On Wed, 12 Sep 2007 23:32:24 -0400
Bruno Lavoie [EMAIL PROTECTED] wrote:
Helllo,
you can also look at Case Studio, this software have a nice reverse
engineering functionnality!
Good luck
Bruno Lavoie
Andrew Hammond a écrit :
Does anyone know where I could find a tool which allows
On Fri, 07 Sep 2007 23:47:40 -
Max [EMAIL PROTECTED] wrote:
Hello,
And pardon me if I posted this question to the wrong list, it seems
this list is the most appropriate.
I am trying to create a table with an array containing foreign keys.
I've searched through the documentation and
I have a php application that needs to query the PK of a table - I'm
currently using this from the information_schema views:
SELECT column_name
FROM information_schema.table_constraints tc
INNER JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_name =
On Wed, 5 Sep 2007 19:08:33 -0400
Merlin Moncure [EMAIL PROTECTED] wrote:
On 9/5/07, Josh Trutwin [EMAIL PROTECTED] wrote:
I have a php application that needs to query the PK of a table -
I'm currently using this from the information_schema views:
try this:
CREATE OR REPLACE VIEW PKEYS
I am curious if there are any rules of thumb for when to index a
foreign key column? I was under the impression that it was always a
good idea to do this based on the fact that you typically join
through a foreign key but after reading the docs I'm not so sure it's
necessary or provides any
On Tue, 28 Aug 2007 13:19:32 -0400
Tom Lane [EMAIL PROTECTED] wrote:
Josh Trutwin [EMAIL PROTECTED] writes:
I am curious if there are any rules of thumb for when to index a
foreign key column?
(You realize of course that there's already an index on the
referenced column, else you
On Fri, 24 Aug 2007 06:31:58 +0100
Mark Cave-Ayland [EMAIL PROTECTED] wrote:
substring(data_field from 'name(.+)\/name')
FWIW, I find the following site extremely useful when trying to
create moderately complex regular expressions: http://www.rexv.org.
Nice site - here's another good one
On Thu, 23 Aug 2007 13:29:46 -0400
Bill Moran [EMAIL PROTECTED] wrote:
Well you haven't given us any indication of data set or what you
are trying to do. However, I can tell you, don't use pconnect,
its broke ;)
Broke? How do you figure?
I asked that question earlier this month - this
Hi - I have the following array field:
SELECT pb_ids FROM pb WHERE id = 123:
pb_id
---
{196,213,215,229,409}
These numbers map to a productid in tblproducts so I figured I could
do this:
SELECT *
FROM tblproducts
WHERE productid = ANY (
SELECT pb_ids
On Tue, 21 Aug 2007 20:15:59 +0200
Pavel Stehule [EMAIL PROTECTED] wrote:
SELECT * FROM ... WHERE 1000 IN (SELECT pb_ids FROM pb WHERE id=123)
or
SELECT * FROM ... WHERE 1000 = ANY(ARRAY(SELECT pb_ids FROM pb
WHERE id=123))
Thanks - another way:
SELECT * FROM ... WHERE 1000 =
On Tue, 21 Aug 2007 14:19:03 -0500
Michael Glaesemann [EMAIL PROTECTED] wrote:
Out of curiosity, what led to the schema design of storing these
pb_id values in an array rather than in a many-to-many table?
You're working against the database server here. The usual way to
define this
On Tue, 21 Aug 2007 21:36:00 +0200
Pavel Stehule [EMAIL PROTECTED] wrote:
it works? no.
pavel=# select 1 from (values(10)) a(i) where i = any((select *
from foo)::int[]);
ERROR: cannot cast type integer to integer[]
LINE 1: ...values(10)) a(i) where i = any((select * from
foo)::int[]);
On Mon, 13 Aug 2007 11:30:37 -0500
Scott Marlowe [EMAIL PROTECTED] wrote:
Oh, one other thing that contributes to the problem
Thanks for the replies - all of this was very useful info.
Josh
---(end of broadcast)---
TIP 2: Don't 'kill -9'
On Mon, 13 Aug 2007 09:44:26 -0500
Erik Jones [EMAIL PROTECTED] wrote:
I'll agree with Scott on this one. (Not that I can recall
specifically ever disagreeing with him before...). Unless you
know all of the potential caveats associated with php's persisent
postgres connections and have a
On Tue, 7 Aug 2007 11:07:39 -0700 (PDT)
novnov [EMAIL PROTECTED] wrote:
snip
Is there any plan to add such a capability to postgres? Is there
deep seated reason why reordering columns can't be handled without
doing a root canal on the database?
Probably because the theory behind the
53 matches
Mail list logo
