From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Mark Zellers <markz(at)adaptiveinsights(dot)com>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Migrating an application with Oracle temporary tables |
Date: | 2019-05-03 06:19:21 |
Message-ID: | 556ca13c06e96fe165ee03d297f42bb405807bed.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, 2019-05-02 at 16:55 +0000, Mark Zellers wrote:
> I thought I needed the prototype table to be able to define functions and procedures that refer to the temporary table but do not create it.
>
> Perhaps my assumption that I need the table to exist (whether as a temporary table or as a permanent table) in
> order to define the function/procedure is incorrect. I'll take a look at that.
You don't need the table to exist at function definition time.
The following works just fine, even if the table does not exist:
CREATE FUNCTION f() RETURNS void LANGUAGE plpgsql AS $$BEGIN PERFORM * FROM notexists; END;$$;
This is because functions are not parsed when they are defined.
> I did find a scenario where this approach does run into trouble. That is, if the function/procedure is executed
> against the permanent table and then you go to run it against a temporary table. In that case, I do get the
> wrong answer, and I haven't yet figured out how to reset that without dropping the procedure and re-defining it.
> For my purposes, that is "good enough" -- I can promise not to run such procedures against the temporary table.
Yes, that would cause a problem.
The SQL statement "DISCARD PLANS" should fix the problem.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
From | Date | Subject | |
---|---|---|---|
Next Message | Igal Sapir | 2019-05-03 06:45:05 | Re: Starting Postgres when there is no disk space |
Previous Message | Guntry Vinod | 2019-05-03 05:48:46 | RE: Back Slash \ issue |