Hello.
I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of auditing.
I think that it's working alright except for the next line:
EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT
new.*';
PostgreSQL keeps telling me: "ERROR: NEW used in query that is not in a
NEW is only plpgsql variable. It isn't visible on SQL level. You
cannot use new.*, you can:
execute 'INSERT ...' || 'VALUES(' || new.a || ',' || new.b
regards
Pavel
2007/8/11, Javier Fonseca V. <[EMAIL PROTECTED]>:
>
>
> Hello.
>
> I'm doing a Trigger Procedure in pl/pgSQL. It makes some k
On Fri, Aug 10, 2007 at 08:13:46PM -0500, Rodrigo De León wrote:
> On 8/10/07, hubert depesz lubaczewski <[EMAIL PROTECTED]> wrote:
> > unfortunatelly this query will be hard to optimize.
>
> Uh, how about
>
> SELECT MAX(t1)
> FROM t1
> WHERE '9849' LIKE t1 || '%';
it will not help much as th
On Friday 10 August 2007 23:30:14 Tom Lane wrote:
> Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> > Is there a way I can have multiple columns in the ORDER BY clause, each
> > with different ASC/DESC-order and still use an index to speed up sorting?
>
> A btree index isn't magic, it's just an
"Pavel Stehule" <[EMAIL PROTECTED]> writes:
> NEW is only plpgsql variable. It isn't visible on SQL level.
Correct, but:
> You cannot use new.*, you can:
> execute 'INSERT ...' || 'VALUES(' || new.a || ',' || new.b
You're both overthinking the problem. In recent releases (at least
since 8.
2007/8/11, Tom Lane <[EMAIL PROTECTED]>:
> "Pavel Stehule" <[EMAIL PROTECTED]> writes:
> > NEW is only plpgsql variable. It isn't visible on SQL level.
>
> Correct, but:
>
> > You cannot use new.*, you can:
> > execute 'INSERT ...' || 'VALUES(' || new.a || ',' || new.b
>
> You're both overthin
Yes Tom, you're right, but the real problem is that I need to use an EXECUTE
statement because my table name is dynamic. In your example, you used logt
as a static table name, and that doesn't need an EXECUTE statement.
So I think that I'll have to rewrite a Trigger Procedure for each table and
t
On Fri, Aug 10, 2007 at 04:53:12PM +0200, Andreas Joseph Krogh wrote:
> I have the following test-case:
>
> CREATE TABLE test(
> name varchar PRIMARY KEY,
> value varchar NOT NULL,
> created timestamp not null
> );
>
> create index test_lowernamevalue_idx ON test ((lower(name) || lower(value)));
On Sat, Aug 11, 2007 at 02:45:09AM -0500, Javier Fonseca V. wrote:
> I think that it's working alright except for the next line:
doing this in plpgsql is very complicated (or even impossible assuming
that any table can have the same trigger). i would rather suggest using
pl/perl - writing somethin
On Saturday 11 August 2007 21:05:22 hubert depesz lubaczewski wrote:
> On Fri, Aug 10, 2007 at 04:53:12PM +0200, Andreas Joseph Krogh wrote:
> > I have the following test-case:
> >
> > CREATE TABLE test(
> > name varchar PRIMARY KEY,
> > value varchar NOT NULL,
> > created timestamp not null
> > );
Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> On Friday 10 August 2007 23:30:14 Tom Lane wrote:
>> Reverse-sorted index columns are possible but not well supported in
>> existing PG releases (you need a custom operator class, and the planner
>> is not all that bright about using them). 8.3 wi
On Saturday 11 August 2007 21:55:49 Tom Lane wrote:
> Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> > On Friday 10 August 2007 23:30:14 Tom Lane wrote:
> >> Reverse-sorted index columns are possible but not well supported in
> >> existing PG releases (you need a custom operator class, and the
12 matches
Mail list logo
