PostgreSQL syntax error from backslashes?

Question

I am debugging a sql file in PostgreSQL's pgAdminIII, and this statement creates a syntax error when executing:

ERROR:  syntax error at or near "v"
LINE 81384: ...n.oid = c.relnamespace  WHERE     c.relkind IN (\'v\') AND n...

The statement:

CREATE OR REPLACE FUNCTION getnextview()   
RETURNS name AS 
' DECLARE
   my_record RECORD;  viewName name; 
BEGIN
FOR my_record IN
  SELECT c.relname
  FROM pg_catalog.pg_class AS c
  LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
  WHERE c.relkind IN (\'v\')
  AND n.nspname NOT IN (\'pg_catalog\', \'pg_toast\')
  AND pg_catalog.pg_table_is_visible(c.oid)
  LIMIT 1
LOOP
   viewName := my_record.relname;  
END LOOP;
RETURN (viewName);
END; '  LANGUAGE 'plpgsql' VOLATILE;

Note, the pg_catalog and pg_toast also error.

score 2 · Accepted Answer · 2012-01-31 21:29:06Z

2

So the full statement does change the picture substantially.

Your problem is the nesting of single quotes. If you use PostgreSQL's "dollar quoting" everything is a lot easier:

CREATE OR REPLACE FUNCTION getnextview() 
  RETURNS name 
AS 
$body$
DECLARE 
    my_record RECORD; 
    viewName name; 
BEGIN 
    FOR my_record IN 
            SELECT c.relname 
            FROM pg_catalog.pg_class AS c 
              LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
            WHERE c.relkind IN ('v') 
              AND n.nspname NOT IN ('pg_catalog', 'pg_toast') 
              AND pg_catalog.pg_table_is_visible(c.oid) 
            LIMIT 1 
    LOOP 
        viewName := my_record.relname; 
    END LOOP; 

    RETURN (viewName); 
END; 
$body$
LANGUAGE 'plpgsql' 
VOLATILE;

edited Jan 31, 2012 at 21:29

answered Jan 31, 2012 at 21:04

user330315

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

2 Comments

user1181323 Over a year ago

I tried that, but it still errored. I then tried it with ''v'' (not a double quote, two single quotes next to each other) and it ran. This wont change the 'v' will it?

user330315 Over a year ago

Then you do need to show the full and exact statement you are running!

Erwin Brandstetter · Accepted Answer · 2012-01-31 22:25:11Z

1

This does the same exactly, just simpler:

CREATE OR REPLACE FUNCTION getnextview()   
RETURNS name AS 
$BODY$

SELECT c.relname 
FROM   pg_catalog.pg_class c
LEFT   JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relkind = 'v'
AND    n.nspname NOT IN ('pg_catalog', 'pg_toast') 
AND    pg_catalog.pg_table_is_visible(c.oid)
LIMIT  1

$BODY$
   LANGUAGE sql STABLE;

edited Jan 31, 2012 at 22:25

answered Jan 31, 2012 at 22:12

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

PostgreSQL syntax error from backslashes?

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related