On Thu, 6 Aug 2009 16:58:02 -0400
Michael Glaesemann 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 saying the <> ope
On Thu, 6 Aug 2009 13:15:57 -0400
Merlin Moncure 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, this synt
On Thu, 6 Aug 2009 13:15:57 -0400
Merlin Moncure 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 try this.
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 trigger_test_upd_set_la
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 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
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 no
On Tue, 10 Mar 2009 12:37:21 +
Gregory Stark 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 libmpfr and a mism
On Mon, 9 Mar 2009 22:01:38 -0700 (MST)
"Leonel Nunez" 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 background and liked to
On Tue, 10 Mar 2009 00:49:32 -0400
Tom Lane wrote:
> Josh Trutwin writes:
> > 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 no re
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
-Wdeclaration-after-stateme
On Fri, 27 Feb 2009 09:34:08 +
Richard Huxton 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 suggestions?
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 p
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 engineeri
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.
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 que
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 (check_aggregate_exists('
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 app
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
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 ques
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 anyon
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
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 us
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 if
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 OFF
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:
SELEC
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 "releas
On Wed, 17 Oct 2007 10:04:21 -0700
David Fetter <[EMAIL PROTECTED]> wrote:
> 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 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
>
On Wed, 17 Oct 2007 17:42:21 +0100
Sam Mason <[EMAIL PROTECTED]> wrote:
> CREATE OR REPLACE FUNCTION array_intersect (array1
> INTEGER[],array2 INTEGER[]) RETURNS INTEGER[] AS $$
> DECLARE
>out INTEGER[];
> BEGIN
> out := '{}'::INTEGER[];
> IF array1 IS NUL
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 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
>R
> 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
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
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
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
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 pos
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
n
On Sat, 08 Sep 2007 11:44:17 +0200
Thorsten Kraus <[EMAIL PROTECTED]> wrote:
> I am looking for a tool which is able to generate a database
> diagramm including the relationships from an existing database
> schema. The only tool I know for this purpose is the Clay database
> plugin for eclipse. Ar
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 wh
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 = my
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 documentati
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 informatio
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 = ccu.constraint_n
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
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 improv
Hi,
Did a little googling but didn't come up with much - does anyone know
if someone made an ERD of either the information_schema or system
catalog (pg_) tables for postgres 8?
Something similar to this for MySQL 5 would be ideal:
http://www.xcdsql.org/MySQL/information_schema/5.1/MySQL_5_1_INFO
On Fri, 24 Aug 2007 06:31:58 +0100
Mark Cave-Ayland <[EMAIL PROTECTED]> wrote:
> > substring(data_field from '(.+)<\/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 o
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 mont
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
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 re
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 = A
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 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 -
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 ha
On Tue, 7 Aug 2007 11:07:39 -0700 (PDT)
novnov <[EMAIL PROTECTED]> wrote:
> 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 relation
55 matches
Mail list logo
