dynamic variable interpolation on queries in plpgsql functions
Coniglio Sgabbiato
table which the name is been obtaind from another query?
A trivial example with no practical purpose invented to show what I mean:
create or replace function test(_schema varchar) returns bigint as $$
declare
var_table_name record;
totalcount bigint;
begin
totalcount:=0;
for var_table_name in select table_name from information_schema.tables
where table_schema=_schema loop
totalcount := totalcount + select count(*) from var_table_name;
end loop;
return totalcount;
end;
$$ language plpgsql;
is there a way to interpolate, for example var_table_name?
Andreas Kretschmer
Sure, you have to use EXECUTE for dynamic SQL.
http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
Coniglio Sgabbiato
> http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
>
> Andreas
Ops, I am sorry, I got confused and put the wrong question, my need is
actually to dynamically generate a query with values retrieved from OLD
and NEW record variables in a trigger, something like this:
the probelmatic point: |
|
V
r_d_table := rtable || ' and ' || col || '=' OLD.<col?> ;
r_i_table := rtable || ' and ' || col || '=' NEW.<col?> ;
to put things clear I am actually thinking about to create an automatic
mechanism to generate materialized views with low impact (actually
without re-generating the entire table but affecting only the affected
rows) below you can find the draft code of two queries, one that do
update the materialized view and another that create one trigger on
every table on which the original view queries against, if you have some
patience to read the functions perhaps can give me some advices (if such
a thing it is possible). I updated the subject of the message
accordingly to the real topic I am looking to.
Thank you very much,
Anselmo Canfora
-- create materialized views based on view based ONLY to tables (no
other views)
-- views to be materialized MUST use aliases for all the tables in
definition
-- table name MUST compare only one time in view definition
create or replace function update_matview(_view_schema varchar,
_view_name varchar) returns trigger as $$
declare
query varchar;
regexp varchar;
m_query varchar;
r_d_table varchar;
r_i_table varchar;
col record;
begin
r_d_table='(select * from ' || TG_SCHEMA_NAME || '.' || TG_TABLE_NAME ||
' where true';
r_i_table:=r_d_table;
for col in select column_name from information_schema.columns where
table_schema=TG_SCHEMA_NAME and table_name=TG_TABLE_NAME loop
r_d_table := rtable || ' and ' || col || '=' OLD.<col> ;
r_i_table := rtable || ' and ' || col || '=' NEW.<col> ;
end loop;
r_d_table:=')';
query := view_definition from information_schema where
table_name=_view_name and table_schema=_view_schema;
regexp:='(' || TG_SCHEMA_NAME || '\.)*' || TG_TABLE_NAME || ' ';
m_query := regexp_replace(query, regexp, r_d_table);
execute m_query;
if TG_OP = 'INSERT' or TG_OP = 'UPDATE' then
m_query := regexp_replace(query, regexp, r_i_table);
execute m_query;
end if;
exception
end;
$$ language plpgsql;
create or replace function create_matview(_view_schema varchar,
_view_name varchar) returns void as $$
declare
qry varchar;
t record;
begin
qry:='create table ' || _view_schema || '.matview_' || _view_name || '
as select * from ' _view_schema || '.' || _view_name;
execute qry;
for t in select distinct table_schema, table_name from
information_schema.view_table_usage loop
qry:='create trigger matview_' || t.table_schema || '_' || t.table_name
|| 'trigger after delete or update or insert on ' || t.table_schema ||
'.' || t.table_name || ' execute procedure update_matview(' ||
_view_schema || ',' || _view_name || ')';
end loop;
end;
$$ language plpgsql;
Laurenz Albe
> Ops, I am sorry, I got confused and put the wrong question, my need is
> actually to dynamically generate a query with values retrieved from OLD
> and NEW record variables in a trigger, something like this:
>
> the probelmatic point: |
> |
> V
>
> r_d_table := rtable || ' and ' || col || '=' OLD.<col?> ;
> r_i_table := rtable || ' and ' || col || '=' NEW.<col?> ;
There is a good page on this in the PostgreSQL Wiki:
http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
I got the example working like this:
CREATE OR REPLACE FUNCTION printnew() RETURNS trigger LANGUAGE plpgsql AS
$$DECLARE
ri RECORD;
t TEXT;
BEGIN
RAISE NOTICE E'\n Operation: %\n Schema: %\n Table: %',
TG_OP,
TG_TABLE_SCHEMA,
TG_TABLE_NAME;
FOR ri IN
SELECT ordinal_position, column_name, data_type
FROM information_schema.columns
WHERE
table_schema = quote_ident(TG_TABLE_SCHEMA)
AND table_name = quote_ident(TG_TABLE_NAME)
ORDER BY ordinal_position
LOOP
EXECUTE 'SELECT (' ||
quote_literal(NEW) || '::' || TG_RELID::regclass ||
').' || quote_ident(ri.column_name)
INTO t;
RAISE NOTICE E'Column\n number: %\n name: %\n type: %\n value: %.',
ri.ordinal_position,
ri.column_name,
ri.data_type,
t;
END LOOP;
RETURN NEW;
END;$$;
This should give you an idea how you could achieve your goal.
Yours,
Laurenz Albe
Coniglio Sgabbiato
yes, I think it is what I need :) many thanks for smart advices!
Just for curiosity I tried to look at the examples in the wiki too, and
did not understand this statement:
EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO t USING NEW;
I dont know what "($1)" means and did not find the clause "using" in the
execute statement:
http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
about the materialized views, I am thinking to use OIDs instead of
comparing all the columns values between NEW/OLD records and original
table, perhaps it could be more direct/fast but dunno if NEW and OLD
contains OID row. What do you think about?
Coniglio Sgabbiato
> about the materialized views, I am thinking to use OIDs instead of
> comparing all the columns values between NEW/OLD records and original
> table, perhaps it could be more direct/fast but dunno if NEW and OLD
> contains OID row. What do you think about?
OID approach cannot work because the view does not have row oid, and in
last versions of PG OID are disabled by default, right?
Laurenz Albe
> Just for curiosity I tried to look at the examples in the wiki too, and did not understand this statement:
>
> EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO t USING NEW;
>
> I dont know what "($1)" means and did not find the clause "using" in the execute statement:
> http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Yes, that is because this is a new feature in 8.4 (as the Wiki says).
It is a dynamic statement with parameters, and what happens is that
the first placeholder $1 gets replaced with the value in NEW,
which is a record type.
Then the appropriate column is picked out, and the result is cast to "text".
> about the materialized views, I am thinking to use OIDs instead of comparing all the columns values between NEW/OLD records and
> original table, perhaps it could be more direct/fast but dunno if NEW and OLD contains OID row. What do you think about?
As you realized, depending on OIDs is not a good idea.
I don't understand why you want to compare the values in NEW
with other rows in the original table, perhaps I don't understand
your design well enough.
Isn't it that you want to write a materialized view that is
updated whenever something on the underlying table changes?
In that case I would expect that you only need to access
the materialized view to change rows in it appropriately.
And if you ensure that you have a useful primary key on the
materialized view table, these operations should be fast,
right?
Yours,
Laurenz Albe
Coniglio Sgabbiato
>
> It is a dynamic statement with parameters, and what happens is that
> the first placeholder $1 gets replaced with the value in NEW,
> which is a record type.
> Then the appropriate column is picked out, and the result is cast to "text".
>
>> about the materialized views, I am thinking to use OIDs instead of comparing all the columns values between NEW/OLD records and
>> original table, perhaps it could be more direct/fast but dunno if NEW and OLD contains OID row. What do you think about?
>
> As you realized, depending on OIDs is not a good idea.
>
> I don't understand why you want to compare the values in NEW
> with other rows in the original table, perhaps I don't understand
> your design well enough.
>
> Isn't it that you want to write a materialized view that is
> updated whenever something on the underlying table changes?
> In that case I would expect that you only need to access
> the materialized view to change rows in it appropriately.
>
> And if you ensure that you have a useful primary key on the
> materialized view table, these operations should be fast,
> right?
right, I was trying to write something generalized to tables without a
primary key. Actually tables without primary key are usually found in
bad designed databases, so it would be reasonable to admit that tables
have always primary keys. I'll explain better what I am trying to do in
my very little spare time. I can automatically create a trigger for
eache table involved in the view I want to materialize:
create or replace function create_matview(_view_schema varchar,
_view_name varchar) returns void as $$
declare
qry varchar;
t record;
begin
qry:='create table ' || quote_ident(_view_schema) || '.materialized_' ||
quote_ident(_view_name) || ' as select * from '
quote_ident(_view_schema) || '.' || _quote_ident(view_name);
execute qry;
for t in select distinct table_schema, table_name from
information_schema.view_table_usage loop
qry:='create trigger materialized_' || quote_ident(t.table_name) || '
trigger after delete or update or insert on ' ||
quote_ident(t.table_schema) || '.' || quote_ident(t.table_name) || '
execute procedure update_matview(' || quote_ident(_view_schema) || ', '
|| quote_ident(_view_name) || ')';
execute qry;
end loop;
end;
$$ language plpgsql;
the next step it is to retrieve
the records of the view affected by the change, and only those, so I
should put in join every record of the trigger with the view, the trick
here (very very dirty) it is to retrieve the definition of the view:
qry := view_definition from information_schema
where table_name=quote_ident(_view_name)
and table_schema=quote_ident(_view_schema);
and then replace the name of the table the trigger has been fired from
with a subquery involving only the record changed, it should work only
under the condition that views to be materialized MUST use aliases and
only aliases for ALL the tables in definition, below an _idea_ of
trigger code:
tbl := quote_ident(TG_SCHEMA_NAME) || '.' || quote_ident(TG_TABLE_NAME);
vw := quote_ident(_view_schema) || '.' || quote_ident(_view_name);
mvw := quote_ident(_view_schema) || '.materialized_' ||
quote_ident(_view_name);
tbl := '(select * from ' || tbl || ' where true';
tple :='';
tple_val :='':
qry := view_definition from information_schema
where table_name=quote_ident(_view_name)
and table_schema=quote_ident(_view_schema);
for col in select c.column_name
from information_schema.table_constraints as t
join information_schema.constraint_column_usage as c
on t.constraint_name=c.constraint_name
and constraint_type='PRIMARY KEY'
and t.table_schema=TG_SCHEMA_NAME
and t.table_name=TG_TABLE_NAME loop
EXECUTE 'SELECT (' ||
quote_literal(OLD) || '::' || TG_RELID::regclass ||
').' || quote_ident(col.column_name)
INTO var;
tple := quote_ident(col.column_name) || ', ';
tple_val := quote_literal(var) || ', ';
end loop;
tple:= '(' || tple || '1)';
tple_val:= '(' || tple_val || '1)';
tqry:='(select * from ' || tbl || ' where ' || tpl || '=' || tpl_val || ')';
rxp := '(' || quote_ident(TG_SCHEMA_NAME) || '\.)*' ||
quote_ident(TG_TABLE_NAME) || ' ';
tqry := regexp_replace(qry, rxp, tqry);
if TG_OP = 'DELETE' or TG_OP = 'UPDATE' then
tqry := 'delete from ' || mvw || ' as _mvw_ using (' || tqry || ') as _dsq_
where _mvw_.col...=_sbq_.col... for every col ';
-- (not sure how it is the best tidy way to put join here)
execute tqry;
end if;
if TG_OP = 'INSERT' or TG_OP = 'UPDATE' then
execute 'insert into ' || mvw || ' select * from ' || tqry || ' as
_row_mat_view';
end if;
what do you think about such a tricky thing?, I know it is a big mess
with big limitations (must use aliases for all the tables in views, it
does not work for views upon views, etc etc) but I would like to get it
working :)
Coniglio Sgabbiato
this one has to be deleted from the code above
Laurenz Albe
>> I don't understand why you want to compare the values in NEW
>> with other rows in the original table, perhaps I don't understand
>> your design well enough.
>
> right, I was trying to write something generalized to tables without a primary key. Actually tables without primary key are
> usually found in bad designed databases, so it would be reasonable to admit that tables have always primary keys. I'll explain
> better what I am trying to do in my very little spare time. I can automatically create a trigger for eache table involved in the
> view I want to materialize:
>
> create or replace function create_matview
[creates a materialized view table and a trigger on every table
that the view that should be materialized depends upon]
> the next step it is to retrieve
> the records of the view affected by the change, and only those, so I should put in join every record of the trigger with the view,
> the trick here (very very dirty) it is to retrieve the definition of the view:
>
> and then replace the name of the table the trigger has been fired from with a subquery involving only the record changed, it
> should work only under the condition that views to be materialized MUST use aliases and only aliases for ALL the tables in
> definition, below an _idea_ of trigger code:
>
[...]
>
> what do you think about such a tricky thing?, I know it is a big mess with big limitations (must use aliases for all the tables in
> views, it does not work for views upon views, etc etc) but I would like to get it working :)
Oh, I see.
For your approach to work, the view must meet many more conditions.
It would not work for views defined like this:
... AS SELECT department, avg(salary) FROM empsal GROUP BY department
because if you run that with only one row of "empsal", you will not
get anything meaningful, or even
... AS SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON ...
because an insert in tab2 will not only lead to new rows in
the result, but also possibly remove rows from the result
(which have NULL values for tab2 entries).
So it will only work for a small amount of very simple views.
And without deep analysis of the defining query it is hard to say
whether it would work for a certain query or not.
Oracle supports something like what you want to implement, called
"on commit fast refresh". I looked at the requirements that a view
must fulfill to be eligible for this, and it is a rather long
and daunting list (if you happen to have Metalink access, that
would be note 222843.1).
I don't think you'll be able to check these conditions automatically,
you'd have to rely on it that the view is "well defined".
I guess your approach would work for little more than views that are
inner joins of tables with only simple conditions (no subqueries,
EXISTS or the like) and without grouping or aggregate functions.
If you *still* think that this is worth your spare time, I guess
that your approach would work.
You'll have to add some more stuff to support UPDATE:
An update will not only add rows to the materialized view, but
also delete some. Maybe it would be easiest to implement it as
delete followed by insert: create a BEFORE UPDATE OR DELETE
trigger that removes all the OLD rows, and an AFTER INSERT
OR UPDATE trigger to add the NEW rows.
And yes, if the original tables have no primary keys or the
query "with one row" would otherwise take very long, your
DML statements on the original tables will become quite expensive...
I can't think of a way around that.
But with all the restrictions from above, it would hardly
be a problem to add the additional restriction that all
tables must have primary keys and indexes on rows on which
foreign keys are defined.
Yours,
Laurenz Albe
Laurenz Albe
> I guess your approach would work for little more than views that are
> inner joins of tables with only simple conditions (no subqueries,
> EXISTS or the like) and without grouping or aggregate functions.
And I forgot to mention:
Usually materialized views are useful for views that are very
complex and expensive. For a query that only consists of some
inner joins, why would you need a materialized view?
Yours,
Laurenz Albe
Coniglio Sgabbiato
> Coniglio Sgabbiato wrote:
>>> I don't understand why you want to compare the values in NEW
>>> with other rows in the original table, perhaps I don't understand
>>> your design well enough.
> [...]
>> right, I was trying to write something generalized to tables without a primary key. Actually tables without primary key are
>> usually found in bad designed databases, so it would be reasonable to admit that tables have always primary keys. I'll explain
>> better what I am trying to do in my very little spare time. I can automatically create a trigger for eache table involved in the
>> view I want to materialize:
>>
>> create or replace function create_matview
>
> [creates a materialized view table and a trigger on every table
> that the view that should be materialized depends upon]
>
>> the next step it is to retrieve
>> the records of the view affected by the change, and only those, so I should put in join every record of the trigger with the view,
>> the trick here (very very dirty) it is to retrieve the definition of the view:
>>
>> and then replace the name of the table the trigger has been fired from with a subquery involving only the record changed, it
>> should work only under the condition that views to be materialized MUST use aliases and only aliases for ALL the tables in
>> definition, below an _idea_ of trigger code:
>>
> [...]
>> what do you think about such a tricky thing?, I know it is a big mess with big limitations (must use aliases for all the tables in
>> views, it does not work for views upon views, etc etc) but I would like to get it working :)
>
> Oh, I see.
>
> For your approach to work, the view must meet many more conditions.
> It would not work for views defined like this:
>
>
> because if you run that with only one row of "empsal", you will not
> get anything meaningful, or even
>
>
> because an insert in tab2 will not only lead to new rows in
> the result, but also possibly remove rows from the result
> (which have NULL values for tab2 entries).
>
> So it will only work for a small amount of very simple views.
> And without deep analysis of the defining query it is hard to say
> whether it would work for a certain query or not.
I did not think about these cases, given this, my approach drives to a
complete meaningless implementation
> Oracle supports something like what you want to implement, called
> "on commit fast refresh". I looked at the requirements that a view
> must fulfill to be eligible for this, and it is a rather long
> and daunting list (if you happen to have Metalink access, that
> would be note 222843.1).
>
> I don't think you'll be able to check these conditions automatically,
> you'd have to rely on it that the view is "well defined".
>
> I guess your approach would work for little more than views that are
> inner joins of tables with only simple conditions (no subqueries,
> EXISTS or the like) and without grouping or aggregate functions.
>
> If you *still* think that this is worth your spare time, I guess
> that your approach would work.
these limitations are too heavy, I think that such a thing would be so
worthless that it is not the case to work on it
> You'll have to add some more stuff to support UPDATE:
> An update will not only add rows to the materialized view, but
> also delete some. Maybe it would be easiest to implement it as
> delete followed by insert: create a BEFORE UPDATE OR DELETE
> trigger that removes all the OLD rows, and an AFTER INSERT
> OR UPDATE trigger to add the NEW rows.
actually the pseudocode I wrote it is intended to do so, you see two
conditional, one "if TG_OP='DELETE' or TG_OP='UPDATE'" followed by one
"if TG_OP='INSERT' or TG_OP='UPDATE'"
> And yes, if the original tables have no primary keys or the
> query "with one row" would otherwise take very long, your
> DML statements on the original tables will become quite expensive...
> I can't think of a way around that.
> But with all the restrictions from above, it would hardly
> be a problem to add the additional restriction that all
> tables must have primary keys and indexes on rows on which
> foreign keys are defined.
so, I'll wait that PG dev group will implement materialized views :)