0

I am trying to assign the number of row as integer to a variable in plpgsql 9.1.
Try 1: nothing gets assigned

maz int := (SELECT count(col1) FROM table WHERE col1 = quote_literal(val1));

Try 2: getting error near INTO

EXECUTE 'SELECT count(col1) FROM table
         WHERE col1 = quote_literal(val1) INTO maz';

EDIT: deleted "in" as it was a typo.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
vedic
  • 117
  • 2
  • 9

1 Answers1

4

Your examples are little bit messy.

The first example is wrong - probably incorrect use of IN keyword, and quote_literal() is absolutely useless in this context. The second one is probably wrong too - if val1 is a variable. plpgsql variables are not visible inside the SQL string in an EXECUTE statement. Next issue is INTO in the SQL string.

postgres=# do $$
           DECLARE rc int;
                   val1 varchar := 'Hello'; 
           BEGIN
             rc := (SELECT count(*) FROM xxx WHERE xxx.v = val1);
             RAISE NOTICE '%', rc;
             EXECUTE 'SELECT count(*) FROM xxx WHERE xxx.v = $1'
             USING val1 INTO rc;
             RAISE NOTICE '%', rc;
           END;
           $$;
NOTICE:  1
NOTICE:  1
DO

quote_literal() is usually necessary for dynamic SQL, when you cannot use a USING clause. In your example:

EXECUTE 'SELECT count(*) FROM xxx WHERE xxx.v = ' || quote_literal(val1)
INTO ...

It protects against SQL injection and ensures proper escaping.

Modern releases have the format() function:

EXECUTE format('SELECT count(*) FROM xxx WHERE xxx.v = %L', val1) INTO ...

But EXECUTE ... USING should be preferred.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • @Erwin I am using quote_literal liberally apart from other measures to protect from sql injection and other threats, but I would greatly appreciate if you could provide more pointers for the same. Thanks – vedic Feb 17 '14 at 07:17
  • @vedic: Pointers for how to protect against SQL injection? [This related answer has more details.](http://stackoverflow.com/questions/10705616/table-name-as-a-postgresql-function-parameter/10711349#10711349) Or [try a search.](http://stackoverflow.com/search?q=[plpgsql]+execute+%22sql+injection%22) – Erwin Brandstetter Feb 17 '14 at 07:30