From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | 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 10:21:15 |
Message-ID: | CAFj8pRCJT+NsGuZhK=oxKejYzbpfBfuOitAfzsm3TZZeKPCCQQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
pá 3. 5. 2019 v 8:19 odesílatel Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
napsal:
> 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.
>
It is not fully correct - function with queries are parsed and syntax check
is done. But semantic check is deferred on run time.
Regards
Pavel
> > 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 | Saupe Stefan | 2019-05-03 11:47:06 | Update row attribute that is part of row level security policy using_expression |
Previous Message | Matthias Apitz | 2019-05-03 10:03:46 | Re: Back Slash \ issue |