Thread: psqlexception syntax error at or near "$"

In attempting to execute ddl that will create functions I am getting errors which will not allow the functions to be created.  In running with the 8.4 driver if I attempt the following:

CREATE OR REPLACE FUNCTION system_info_fnc01() RETURNS trigger AS
$$
declare
  iCnt       integer;
begin
  select count(*) into  iCnt from  system_info;
  if ( iCnt > 0 ) then
     raise exception ''System Info record already present'';
  end if;
  return new;
end;
$$
LANGUAGE 'plpgsql' IMMUTABLE

I get an error with the $$.  In substituting an ' for $$ the following occurs:
unterminated quoted string at or near "' declare iCnt integer"

If I then set the delimiter to something like '|' I still get the unterminated quote string problem. Could someone tell me how to get around this problem?

Thanks

Steven Dahlin <pgdb.sldahlin@gmail.com> wrote:

> CREATE OR REPLACE FUNCTION system_info_fnc01() RETURNS trigger AS
> $$

>      raise exception ''System Info record already present'';

> $$

When you use dollar quoting you should not double your apostrophes.

Try:

     raise exception 'System Info record already present';

-Kevin

[Please keep the list copied and please don't top-post.]
[Also, this thread really belongs on pgsql-general or pgsql-novice;
it doesn't seem to have anything to do with JDBC, so I'm blind
copying the JDBC list and posting to general to move it.]

Steven Dahlin <pgdb.sldahlin@gmail.com> wrote:
> Kevin Grittner <Kevin.Grittner@wicourts.gov>wrote:
>
>>> CREATE OR REPLACE FUNCTION system_info_fnc01() RETURNS trigger
>>> AS $$
>>
>>>      raise exception ''System Info record already present'';
>>
>>> $$
>>
>> When you use dollar quoting you should not double your
>> apostrophes.
>>
>> Try:
>>
>>     raise exception 'System Info record already present';

> That makes no difference.

It seems to make a difference on my machine:

test=# CREATE OR REPLACE FUNCTION system_info_fnc01() RETURNS
trigger AS
test-# $$
test$# declare
test$#   iCnt       integer;
test$# begin
test$#   select count(*) into  iCnt from  system_info;
test$#   if ( iCnt > 0 ) then
test$#      raise exception ''System Info record already present'';
test$#   end if;
test$#   return new;
test$# end;
test$# $$
test-# LANGUAGE 'plpgsql' IMMUTABLE;
ERROR:  syntax error at or near "System"
LINE 8:      raise exception ''System Info record already present'';
                               ^
test=# CREATE OR REPLACE FUNCTION system_info_fnc01() RETURNS
trigger AS
test-# $$
test$# declare
test$#   iCnt       integer;
test$# begin
test$#   select count(*) into  iCnt from  system_info;
test$#   if ( iCnt > 0 ) then
test$#      raise exception 'System Info record already present';
test$#   end if;
test$#   return new;
test$# end;
test$# $$
test-# LANGUAGE 'plpgsql' IMMUTABLE;
CREATE FUNCTION

If you're getting different results, please copy/paste actual code.

What version of PostgreSQL are you running?

-Kevin