Thread: Common question: what's wrong

This is a trigger function:

CREATE FUNCTION public.trigger_null2default() RETURNS trigger AS 'BEGIN
IF TG_RELNAME = ''g_oferty'' THEN
    IF NEW.id_oferta ISNULL THEN
        NEW.id_oferta = DEFAULT ;
    END IF;
END IF;
RETURN NEW;
END;'  LANGUAGE 'plpgsql' IMMUTABLE;

There is an error near DEFAULT when firing trigger (on insert)
How can I set defaults without doing INSERT INTO 'g_oferta' (DEFAULT, NEW...?

Martin

On Jan 13, 2005, at 8:32 AM, przygoda@klub.chip.pl wrote:

> CREATE FUNCTION public.trigger_null2default() RETURNS trigger AS 'BEGIN
> IF TG_RELNAME = ''g_oferty'' THEN
>     IF NEW.id_oferta ISNULL THEN
>         NEW.id_oferta = DEFAULT ;
>     END IF;
> END IF;
> RETURN NEW;
> END;'  LANGUAGE 'plpgsql' IMMUTABLE;


I don't know if this will solve it, but the value assignment should use
":=" not "="


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

I believe that if you want the default value in new.id_oferta, then do
nothing. If the value is not set, the default will be applied. I don't think
you can do it the way you are tying to.

On Thursday 13 January 2005 08:32 am, przygoda@klub.chip.pl saith:
> This is a trigger function:
>
> CREATE FUNCTION public.trigger_null2default() RETURNS trigger AS 'BEGIN
> IF TG_RELNAME = ''g_oferty'' THEN
>     IF NEW.id_oferta ISNULL THEN
>         NEW.id_oferta = DEFAULT ;
>     END IF;
> END IF;
> RETURN NEW;
> END;'  LANGUAGE 'plpgsql' IMMUTABLE;
>
> There is an error near DEFAULT when firing trigger (on insert)
> How can I set defaults without doing INSERT INTO 'g_oferta' (DEFAULT,
> NEW...?
>
> Martin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

--
 Work: 1-336-372-6812
 Cell: 1-336-363-4719
email: terry@esc1.com

Thank to Terry I've tried to write rule instead of trigger but have
another problem.

CREATE RULE g_oferty_id_oferty_rule AS ON INSERT TO g_oferty WHERE
(new.id_oferta IS NULL) DO INSTEAD INSERT INTO g_oferty (id_oferta, ...)
VALUES (DEFAULT, new.d..);

But got ' infinite recursion detected in rules for relation "g_oferty" '
error. The second insert should not fire this Rule...


Martin

Is is possible to maka a PL/pgsql function which can receive a random SQL
query as input variable?

Like this:

SELECT save_query ('SELECT * FROM objects, names WHERE objects.id =
names.id);

And than execute the SQL query inside the function.

Tjibbe

See this section of the manual:

http://wwwmaster.postgresql.org/docs/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Sean

----- Original Message -----
From: "Tjibbe Rijpma" <t.b.rijpma@student.tudelft.nl>
To: "PostgreSQL Novice" <pgsql-novice@postgresql.org>
Sent: Monday, January 17, 2005 8:33 AM
Subject: [NOVICE] Sql query as input variable in a PL/pgsql function?


> Is is possible to maka a PL/pgsql function which can receive a random SQL
> query as input variable?
>
> Like this:
>
> SELECT save_query ('SELECT * FROM objects, names WHERE objects.id =
> names.id);
>
> And than execute the SQL query inside the function.
>
> Tjibbe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>