Re: delay of function - Mailing list pgsql-novice

On Wed, Mar 01, 2006 at 05:57:34PM +0100,  Jaromr Kamler wrote:
> I use PostGIS ... .

If you have PostGIS-specific questions then you might be better off
asking them in the postgis-users mailing list.

http://postgis.refractions.net/mailman/listinfo/postgis-users

> I have table:
> CREATE TABLE basic_metadata(name text,size text, ......);
>
> Inside I want insert some records (name of new table, size of new table and etc.), so I created trigger on the table
geometry_columns:
> CREATE TRIGGER ins_metdta AFTER INSERT ON geometry_columns FOR EACH ROW EXECUTE PROCEDURE insert_new();

Non-PostGIS users might not understand what you're doing here.  To
add a PostGIS geometry column to a table you call the AddGeometryColumn
function, which among other things inserts a record into geometry_columns.
It looks like you want that insert to trigger additional bookkeeping.

> CREATE OR REPLACE FUNCTION insert_new() RETURNS trigger AS $$
> DECLARE
> str text;
> BEGIN
>   str := 'insert into basic_metadata(name,size) values
(\''||new.f_table_name||'\',\''||pg_size_pretty(pg_relation_size(new.f_table_name))||'\');';
>   raise notice '%', str;
>   execute str;
>   return null;
> END;
> $$ LANGUAGE PLPGSQL;

Is there a reason you're using EXECUTE instead of inserting the
values directly?  If you do use EXECUTE then quote the values you're
inserting with quote_literal() instead of adding the quotes yourself.
Simplistic quoting such as the above can result in syntax errors
and make you susceptible to SQL injection attacks (which are variants
of the same problem: unanticipated data resulting in unanticipated
behavior).

What's the purpose of inserting the relation size?  For a new
relation it'll be zero, and any in case it'll be obsolete as soon
as you start inserting, updating, and deleting data.

> Every time, when is something inserted into table geometry_columns
> would trigger insert some data into the table basic_metadata, but
> it fail, becouse in that time when is inserted name of new table
> into geometry_columns is not created that new table physically and
> therefore I can't call function pg_relation_size(). It is created
> after that. So I need wait with calling that trigger until that new
> table is done or ...... ????? Does anybody have some solution?

Can you explain what you're doing and what error you're getting?
Ordinarily the table should exist by the time you call a PostGIS
function that inserts into geometry_columns, but there might be a
circumstance I'm overlooking.

--
Michael Fuhr