Tag Info

New answers tagged

0

its okay, and sorry for the bad explanation. i got it to work as shown below: CREATE OR REPLACE FUNCTION tf_xy_point() RETURNS trigger AS $BODY$BEGIN IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN new.easting := ST_X(new.wkb_geometry); new.northing := ST_Y(new.wkb_geometry); RETURN NEW; END IF; END;$BODY$ LANGUAGE plpgsql VOLATILE COST ...


1

To me, it looks like you're on the right track, if I'm understanding your question clearly (which I'm not sure that I am. :P ) To me, it looks like you simply need a DECLARE block, where you are declaring variable values which will persist throughout the function block. Add DECLARE CREATE OR REPLACE FUNCTION insaft_function() RETURNS TRIGGER AS DECLARE ...


3

While passing integer numbers, you can either cast the whole array: TG_ARGV::int[] Or you can cast an element, then it must be the element type: TG_ARGV[0]::int I used it that way in my answer to your previous question: SELECT in trigger function in two tables However, you are not passing integer numbers, but the text representation of integer ...


2

Yes that's possible. Create the function with the user owning all those tables and use the SECURITY DEFINER modifier. SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it. create or replace function foo() returns void as $body$ ... $body$ language plpgsql security definer; Also see the ...


2

You have an unresolved naming conflict. You must be using an old version of Postgres without declaring it. Or you are operating with non-default configuration setting. Here you declare a variable named measurement_id: DECLARE measurement_id INTEGER; It's a folly to use ambiguous variable names to begin with. If you do it anyway, you ...


1

Actually, this is all you need: NEW := jsonb_populate_record(NEW, NEW.json); Per documentation: jsonb_populate_record(base anyelement, from_json jsonb) Expands the object in from_json to a row whose columns match the record type defined by base (see note below). What's not documented: The row provided as first argument retains all values that ...


8

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or in the start of the function definition, declaring how you want such conflicts to be resolved (the 3 possible values are error (the default), use_variable and use_column): CREATE OR REPLACE FUNCTION ...


2

I put in a little time to try and develop an answer for this question which may fit your needs, but since I don't have detailed criteria, it may not be perfect. Hopefully, though, it is close enough so that you can manipulate to meet your design needs. Initial assumptions To begin, I had to make a few initial assumptions to design the algorithm. 1) When ...


3

Dynamic fields are notoriously difficult in plpgsql. In particular there's no way we can write new.variable := something where variable stands for a column name. See How to set value of composite variable field using dynamic SQL for ways that involve querying the catalog at runtime. Personally, I'd suggest a simpler solution with the plv8 language. CREATE ...



Top 50 recent answers are included