Thread: PLPG

PLPG

From
alfranio correia junior
Date:
How do I create a PLPG procedure by means of a statement ?
For instance:

private static String createFunction = "CREATE OR REPLACE FUNCTION
process_audit_customer() RETURNS TRIGGER AS \n"
            + "$process_audit$ \n"
            + "BEGIN \n"
            + "IF (TG_OP = 'DELETE') THEN \n"
            + "INSERT INTO tmp_customer SELECT OLD.*, 'D', now(), user\\; \n"
            + "RETURN OLD\\; \n"
            + "ELSIF (TG_OP = 'UPDATE') THEN \n"
            + "INSERT INTO tmp_customer SELECT  OLD.*, 'U', now(), user\\; \n"
            + "RETURN NEW\\; \n"
            + "ELSIF (TG_OP = 'INSERT') THEN \n"
            + "INSERT INTO tmp_customer SELECT  NEW.*, 'I', now(), user\\; \n"
            + "RETURN NEW\\; \n"
            + "END IF\\; \n"
            + "RETURN NULL\\; \n"
            + "END\\; \n"
            + "$process_audit$ \n" + "LANGUAGE plpgsql SECURITY DEFINER;";


When I try to execute such statement an exception is raised:

ERROR: unterminated dollar-quoted string at or near "$process_audit$

BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO tmp_customer SELECT OLD.*, 'D', now(), user


Does anybody have a suggestion ?

Regards,

Alfranio.


Re: PLPG

From
Dave Cramer
Date:
handling dollar quoted functions is a recent addition to the driver.

You should probably build the driver from source if you really need it.

Dave
On 28-Nov-06, at 9:04 AM, alfranio correia junior wrote:

> How do I create a PLPG procedure by means of a statement ?
> For instance:
>
> private static String createFunction = "CREATE OR REPLACE FUNCTION
> process_audit_customer() RETURNS TRIGGER AS \n"
>             + "$process_audit$ \n"
>             + "BEGIN \n"
>             + "IF (TG_OP = 'DELETE') THEN \n"
>             + "INSERT INTO tmp_customer SELECT OLD.*, 'D', now(), user\\; \n"
>             + "RETURN OLD\\; \n"
>             + "ELSIF (TG_OP = 'UPDATE') THEN \n"
>             + "INSERT INTO tmp_customer SELECT  OLD.*, 'U', now(), user\\; \n"
>             + "RETURN NEW\\; \n"
>             + "ELSIF (TG_OP = 'INSERT') THEN \n"
>             + "INSERT INTO tmp_customer SELECT  NEW.*, 'I', now(), user\\; \n"
>             + "RETURN NEW\\; \n"
>             + "END IF\\; \n"
>             + "RETURN NULL\\; \n"
>             + "END\\; \n"
>             + "$process_audit$ \n" + "LANGUAGE plpgsql SECURITY DEFINER;";
>
>
> When I try to execute such statement an exception is raised:
>
> ERROR: unterminated dollar-quoted string at or near "$process_audit$
>
> BEGIN
> IF (TG_OP = 'DELETE') THEN
> INSERT INTO tmp_customer SELECT OLD.*, 'D', now(), user
>
>
> Does anybody have a suggestion ?
>
> Regards,
>
> Alfranio.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>