Functions with plpgsql and security

[drewbietron]

I am trying to format with no extra options added and using the pgFormatter vscode extension. But when the formatter gets to my first function declaration, it is doing something with the $$, or the plpgsql language that is breaking the sql file.

This is the sql prior to the formatter, which runs with no errors.

create function public.product_has_state("product_state", "product_state") 
returns boolean
language plpgsql 
security definer set search_path = public
as $$
begin
  return $1 = $2;
end;
$$;

alter table public.products
  enable row level security;

Which looks likes this in the editor

After formatting it turns it into

CREATE FUNCTION public.product_has_state ("product_state", "product_state")
    RETURNS boolean
    LANGUAGE plpgsql
    SECURITY DEFINER
    SET search_path = public
    AS $$
BEGIN
    RETURN $1 = $2;
END;
$$;

ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;

Which looks like this

And then errors on the function line saying syntax error at or near "RETURN"

Any help would be appreciated.

[drewbietron]

Looks like its the uppercasing of AS in the as $$ that is causing the syntax error. Is there a way to disable that?

[mdr1384]

IDK but it seems to work fine for me. I just copy/pasted your "after formatting" blob into psql:

$ psql -U postgres
Pager usage is off.
Null display is "(null)".
psql (13.7)
Type "help" for help.

postgres# create type "product_state" as enum('a', 'b', 'c');
CREATE TYPE
postgres# CREATE FUNCTION public.product_has_state ("product_state", "product_state")
postgres# RETURNS boolean
postgres# LANGUAGE plpgsql
postgres# SECURITY DEFINER
postgres# SET search_path = public
postgres# AS $$
postgres# BEGIN
postgres# RETURN $1 = $2;
postgres# END;
postgres# $$;
CREATE FUNCTION
postgres# select product_has_state('b', 'c');
 product_has_state
-------------------
 f
(1 row)
postgres# 

Also @drewbietron - keywords are case-insensitive in SQL and PLPGSQL.

[darold]

@drewbietron I don't see any formatting error here, the output code is the same than the input.