using DEFAULT values for IN parameters in functions produces PG::SyntaxError

Issue #256 invalid
Allen Hewes
created an issue

Using Rails 3.x / Ruby 2.1.X / pg 0.19.0

When using DEFAULT values for input parameters in PL/pgSQL functions, the pg driver will produce a PG::SyntaxError when called via Rails with Squirm (SELCT * FROM <FUNCTION>; method).

-- function stanza:
create or replace function public.fn_get_haz_waste_price (
  in _dealer_id character varying,
  in _part_price numeric(13,2) default 0,
  in _labor_price numeric(13,2) default 0
) returns numeric(13,2) as
$proc$
...
end;
$proc$
language plpgsql;

produces error when called:

PG::SyntaxError (ERROR:  syntax error at or near "DEFAULT"
LINE 1: ...z_waste_price"($1::character varying, $2::numeric DEFAULT 0,...
                                                             ^
):                                                             ^

I worked around this with:

  _part_price := coalesce(_part_price, 0);
  _labor_price := coalesce(_labor_price, 0);

Comments (8)

  1. Allen Hewes reporter

    test case from Aqua Data Studio.

    create or replace function public.fn_get_haz_waste_price (
      in _dealer_id character varying,
      in _part_price numeric(13,2) default 0,
      in _labor_price numeric(13,2) default 0
    ) returns numeric(13,2) as
    $proc$
    

    invocations:

    select * from public.fn_get_haz_waste_price('177D', 0, 0);
    go
    select * from public.fn_get_haz_waste_price('177D', null, null);
    go
    select * from public.fn_get_haz_waste_price('177D');
    go
    

    output:

     1 record(s) selected [Fetch MetaData: 1ms] [Fetch Data: 0ms] 
    
     [Executed: 1/25/2017 6:29:24 PM] [Execution: 1ms] 
    
     1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms] 
    
     [Executed: 1/25/2017 6:29:24 PM] [Execution: 1ms] 
    
     1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms] 
    
     [Executed: 1/25/2017 6:29:24 PM] [Execution: 1ms] 
    

    ruby-pg-functions-with-default-values.png

  2. Allen Hewes reporter

    I am good here. We decided to not use squirm_rails and did our own little helper using "SELECT FROM <function> <parameters> AS result".

    We were using the documented squirm_rails way: declare your function via "procedure <function>" in your model and then use it like a Ruby method: "<function>(param1, param2, param3)".

    I noted how I worked around this by using coalesce() in my function block vs default in the function stanza. Lars was probably after the SQL being sent over the wire which was something I didn't capture at the time. I only grabbed the pg Rubygem error. I didn't go into the squirm_rails / squirm layers to find out how it's invoking the function.

  3. Log in to comment