Postgresql stored procedure execute error

Question

I created a stored procedure. The function is created successfully. When I call function I get an error. How can I solve this problem?

The error is

ERROR: unrecognized conversion type specifier "a" CONTEXT: PL/pgSQL function dwgcould.updatescale(integer,integer) line 6 at EXECUTE statement ********** Error ********** ERROR: unrecognized conversion type specifier "a" SQL state: 22023 Context: PL/pgSQL function dwgcould.updatescale(integer,integer) line 6 at EXECUTE statement

CREATE OR REPLACE FUNCTION scale(IN id integer, IN scale integer) RETURNS integer
AS $$
DECLARE 
    result int;
BEGIN
  IF (SELECT COUNT(*) FROM pg_tables where tablename = format('table_%s_id',id)) > 0 then
    EXECUTE format('update table_%s_id  set geom = ST_Scale(geom, %a, %a',id, scale, scale) using id, scale;
    EXECUTE format('update table_&s_id2  set geom = ST_Scale(geom, %a, %a',id, scale, scale) using id, scale;
    IF FOUND THEN 
        result:= 1;
        return result;
    ELSE 
        result:=0;
        return result;
    END IF; 
  ELSE 
    result:=2;
    return result;
  END IF;
END;
$$ LANGUAGE plpgsql;

How can I solve this problem? By not using a %a specifier. — wildplasser
– wildplasser, Commented Mar 1, 2017 at 11:52
Unrelated, but: the result variable is useless. You can simply do return 1; or return 0; — user330315
– user330315, Commented Mar 1, 2017 at 12:00

Patrick · Accepted Answer · 2017-03-01 12:25:03Z

2

You mix up the use of positional parameters in format() and variables for substitution in the EXECUTE command:

 EXECUTE format('update table_%s_id set geom = ST_Scale(geom, %s, %s)', id, scale, scale);

If you want to return a row_idfrom the `EXECUTE command, the you should explicitly specify that in the UPDATE query:

CREATE OR REPLACE FUNCTION scale(id integer, scale integer) RETURNS integer AS $$
DECLARE 
    result integer;
BEGIN
  IF (SELECT count(*) FROM pg_tables WHERE tablename = format('table_%s_id',id)) > 0 THEN
    EXECUTE format('UPDATE table_%s_id SET geom = ST_Scale(geom, %s, %s)', id, scale, scale) using id, scale;
    EXECUTE format('UPDATE table_&s_id2 SET geom = ST_Scale(geom, %s, %s)
                    RETURNING row_id',id, scale, scale) INTO result;
    RETURN result;
  END IF;
  RETURN 2;
END;
$$ LANGUAGE plpgsql;

edited Mar 1, 2017 at 12:25

answered Mar 1, 2017 at 11:54

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fatih Over a year ago

Thank you so much it is working. But when I use directly query for example update table_10_10 set geom = ST_Scale(geom, 150, 150). updated rows is 346 but result is returning 0.

Fatih Over a year ago

I dont' have row_id so I get an error. I just want to do if query is done return 1 else return 0. I think execute format doesnt work

Randall Over a year ago

@Faith in your Question, you used FOUND. I would think that would work, but also see postgresql.org/docs/current/… - you can also use GET DIAGNOSTICS integer_var = ROW_COUNT;

Collectives™ on Stack Overflow

Postgresql stored procedure execute error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related