Re: when to use "execute" in plpgsql?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Merlin Moncure wrote:
> I'm not completely sure what the problem is because this is light on
> detail, but here's a what I bet the problem is. static (that is, not
> EXECUTE-ed) queries in pl/pgsql functions convert table references in
> the function body to fixed 'pointers' to actual tables that are always
> schema qualified. Once the function is run the first time and the
> plan generated, changing the schema will have no bearing on which
> tables are used. Thus, the function will not 'float' with the current
> schema search path setting.

We had similar problems to this when using temp tables in a function.
Because the temp tables disappear and pg stores the oid, the second time
it is run we were getting an error message. I posted about it on Oct 10.
2005, subject: strange error.

My experience in this is just from 8.0 so I don't know if anything has
changed. In general, if tables are created or deleted within the
function then you want to use execute, as well as if the tables are
recreated in the course of a normal workflow. For example, if you have a
table that you regenerate once a day (by drop and create) you will not
want to reference that table in a view or function.

Obviously if you want to run dynamic code you also need to use execute.

Sim
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkmqnSkACgkQjDX6szCBa+o77gCgjB7W+4tIYZVPtEvaF1Uj3QBC
fPcAoOubAAC9dr5opTRyFsyUfLq6ojQF
=q5NU
-----END PGP SIGNATURE-----