Postgres Pro
Downloads
Home   >   mailing lists

Thread: 7.1 PL/pgSQL EXECUTE Command

7.1 PL/pgSQL EXECUTE Command

From
Brian Troxell
Date:
Just a quick question that has been burning an entire day for me....

I know that the beta 7.1 release adds the capability to do dynamic
queries in PL/pgSQL using the new EXECUTE command.

The problem is, nowhere is it listed how to use the new command.....not
syntax, no format, no nothing. I've tried my best guesses and have
gotten several errors, which isn't surprising.

This is my last-gasp effort to find out how to use the secretive new
functionality. If anyone at all can help, I'd be very grateful.

Thanks.

Re: 7.1 PL/pgSQL EXECUTE Command

From
"Dominic J. Eidson"
Date:
On Wed, 3 Jan 2001, Brian Troxell wrote:

> I know that the beta 7.1 release adds the capability to do dynamic
> queries in PL/pgSQL using the new EXECUTE command.

From one of my triggers:

-- Create trigger function to be run upon deletes
CREATE FUNCTION spares_dbs_delete_trig() RETURNS opaque AS '
BEGIN

  EXECUTE '' DROP TRIGGER spares_'' || OLD.dbs_name || ''_update_trig ON
    '' || OLD.dbs_name || '';'';

  EXECUTE '' DROP TABLE '' || OLD.dbs_name || '';'';
  EXECUTE '' DROP SEQUENCE '' || OLD.dbs_name || ''_'' || OLD.dbs_name ||
    ''_id_seq;'';

  RETURN OLD;
END;
' LANGUAGE 'plpgsql';

Assuming that OLD.dbs_name == 'site1', will do the following:

DROP TRIGGER spares_site1_update_trig ON site1;
DROP TABLE site1;
DROP SEQUENCE site1_site1_id_seq;

As to what in the world I'm doing... don't ask. It's messy :)

--
Dominic J. Eidson
                                        "Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/              http://www.the-infinite.org/~dominic/

Re: 7.1 PL/pgSQL EXECUTE Command

From
Tom Lane
Date:
"Dominic J. Eidson" <sauron@the-infinite.org> writes:
>   EXECUTE '' DROP TRIGGER spares_'' || OLD.dbs_name || ''_update_trig ON
>     '' || OLD.dbs_name || '';'';

The trailing semicolon in the EXECUTE string is unnecessary, which
allows for at least a little less cruft in examples like this.

BTW, there are two new string functions quote_ident and quote_literal
that are designed for use in constructing query strings.  You can think
of them as "surround with double quotes" and "surround with single
quotes" if you like, but they are smarter than that: they also know
about escaping embedded quotes and backslashes.  So, for example, if
you want to write an EXECUTE statement that updates an arbitrary field
to an arbitrary string value, you could do something like

    EXECUTE ''UPDATE table SET '' || quote_identifier(fieldname) ||
        '' = '' || quote_literal(newvalue) || '' WHERE ...'';

This would not fail in the presence of mixed-case fieldnames or embedded
quotes in the value, as would the too-simplistic attempt:

    EXECUTE ''UPDATE table SET '' || fieldname ||
        '' = '''''' || newvalue || '''''' WHERE ...'';

None of this stuff is in the docs yet :-(.  Seems we've been a tad
sloppy about adding documentation for new features this time around.
Anyone want to submit a documentation patch to cover this stuff?

            regards, tom lane

Re: 7.1 PL/pgSQL EXECUTE Command

From
Bruce Momjian
Date:
>     EXECUTE ''UPDATE table SET '' || fieldname ||
>         '' = '''''' || newvalue || '''''' WHERE ...'';
>
> None of this stuff is in the docs yet :-(.  Seems we've been a tad
> sloppy about adding documentation for new features this time around.
> Anyone want to submit a documentation patch to cover this stuff?

I still need to go through the HISTORY file and make sure all the
user-visible stuff is in the docs, unless someone else does this first.


--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026