From: | Durumdara <durumdara(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | PLPGSQL: when the local variable used and when the table field? |
Date: | 2020-03-25 13:08:56 |
Message-ID: | CAEcMXhn1qd1wZYggDOOtyAYjTKLBtUMdzAtNqSpEcBCp=rLktw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello!
PLPGSQL allows me to write simple queries and updates without execute +
using.
F.e:
DECLARE t text; anytype text;
BEGIN
...
select nev into t from anytable where type = anytype;
...
insert into bla (id, name, type)
select id, name, anytype from bla
...
But this method is seems to be unsafe for me.
How the compiler knows what value I want to use?
If bla table has "anytype" field, it can use that field, or it can use
local variable too.
Which has more priority/precedency?
For example:
1.) I have this proc (installed):
select nev into t from anytable where type = anytype;
Later I add anytype column to anytable.
2.) Or this:
insert into bla (id, name, type)
select id, name, anytype from bla
Later I add anytype to bla.
What would happen?
The working procedure makes error, because it have more possible source of
data (local variable, and table field)?
Or it uses the local variable, because in the scope it has more precedency?
Ok, sometimes I can use prefix for table:
select bla.id, bla.name, anytype from bla
But when I WANT to use local variable (as constant) and later the bla
extended with anytype column, it would be source of conflict.
Somewhere I force to use "_" prefix for local variables to be sure in
result.
Do you know any info about the background?
Thanks for answer!
Best regards
dd
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2020-03-25 13:50:41 | Re: PLPGSQL: when the local variable used and when the table field? |
Previous Message | Ekaterina Amez | 2020-03-25 13:06:43 | Re: How to plpgsql scripting |