search

kputnam / piggly

PL/pgSQL stored procedure code coverage tool
Other
69 stars 14 forks source link

issue with table return types #34

Closed born-in-brooklyn closed 6 years ago

born-in-brooklyn commented 6 years ago

piggly seems to have problems with procedures that return table types. A procedure with this signature:

CREATE OR REPLACE FUNCTION risk.func_agrt_firm_params(IN p_firm_id integer DEFAULT NULL::integer)
  RETURNS TABLE(firm_id integer, shared_fl boolean, district_id integer, finop_bus_act_fl boolean, sp_bus_act_fl boolean, underwriting_fl boolean, net_capital numeric) AS
$BODY$
BEGIN
-- there be dragons here
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

produces a cache index like this:

- !ruby/object:Piggly::Dumper::SkeletonProcedure
  arg_defaults:
  - NULL::integer
  arg_types:
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: integer
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: integer
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: boolean
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: integer
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: boolean
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: boolean
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: boolean
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: numeric
    array: ''
  arg_names:
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: p_firm_id
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: firm_id
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: shared_fl
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: district_id
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: finop_bus_act_fl
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: sp_bus_act_fl
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: underwriting_fl
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: net_capital
  arg_modes:
  - in
  - t
  - t
  - t
  - t
  - t
  - t
  - t
  setof: true
  volatility: volatile
  type: !ruby/object:Piggly::Dumper::QualifiedType
    schema: pg_catalog
    name: record
    array: ''
  secdef: false
  strict: false
  name: !ruby/object:Piggly::Dumper::QualifiedName
    schema: risk
    name: func_agrt_firm_params
  oid: '808889'
  identifier: fe2d4b76b3d641208f28a6c0cea52d80
- !ruby/object:Piggly::Dumper::SkeletonProcedure
  arg_defaults:
  - 
  - 
  - 
  - 
  - 
  - 
  - 
  arg_types:
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: integer
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: boolean
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: integer
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: boolean
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: boolean
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: boolean
    array: ''
  - !ruby/object:Piggly::Dumper::QualifiedType
    schema: 
    name: numeric
    array: ''
  arg_names:
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: p_firm_id
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: p_shared_fl
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: p_district_id
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: p_finop_bus_act_fl
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: p_sp_bus_act_fl
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: p_underwriting_fl
  - !ruby/object:Piggly::Dumper::QualifiedName
    schema: 
    name: p_net_capital
  arg_modes:
  - in
  - in
  - in
  - in
  - in
  - in
  - in
  setof: false
  volatility: volatile
  type: !ruby/object:Piggly::Dumper::QualifiedType
    schema: risk
    name: type_agrt_firm_oprnl_aplbl
    array: ''
  secdef: false
  strict: false
  name: !ruby/object:Piggly::Dumper::QualifiedName
    schema: risk
    name: func_agrt_calc_firm_aplbl
  oid: '808890'
  identifier: 101ba1ab688d27f31a2abcaf24e9028b

piggly trace produces this error:

C:/Ruby22/lib/ruby/gems/2.2.0/gems/piggly-2.2.1/lib/piggly/installer.rb:50:in `exec': ERROR:  syntax error at or near ""int4"" (PG::SyntaxError)
LINE 1: ...irm_id" "int4" DEFAULT NULL::integer, t "firm_id" "int4", t ...
                                                             ^

Error installing traced procedure risk.func_agrt_firm_params from C:/Users/K26962/RAW/comp-workspace/src/test/resources/sql/piggly/cache/Dumper/fe2d4b76b3d641208f28a6c0cea52d80.plpgsql
        from C:/Ruby22/lib/ruby/gems/2.2.0/gems/piggly-2.2.1/lib/piggly/installer.rb:50:in `trace'
        from C:/Ruby22/lib/ruby/gems/2.2.0/gems/piggly-2.2.1/lib/piggly/installer.rb:16:in `block in install'
        from C:/Ruby22/lib/ruby/gems/2.2.0/gems/piggly-2.2.1/lib/piggly/installer.rb:14:in `each'
        from C:/Ruby22/lib/ruby/gems/2.2.0/gems/piggly-2.2.1/lib/piggly/installer.rb:14:in `install'
        from C:/Ruby22/lib/ruby/gems/2.2.0/gems/piggly-2.2.1/lib/piggly/command/trace.rb:58:in `install'
        from C:/Ruby22/lib/ruby/gems/2.2.0/gems/piggly-2.2.1/lib/piggly/command/trace.rb:33:in `main'
        from C:/Ruby22/lib/ruby/gems/2.2.0/gems/piggly-2.2.1/lib/piggly/command/base.rb:15:in `main'
        from C:/Ruby22/lib/ruby/gems/2.2.0/gems/piggly-2.2.1/bin/piggly:8:in `<top (required)>'
        from C:/Ruby22/bin/piggly:23:in `load'
        from C:/Ruby22/bin/piggly:23:in `<main>'

It seems like the rewriting is putting one of the table columns in as another input parameter instead of creating a table parameter.

kputnam commented 6 years ago

Thanks for the helpful bug report! I hadn't encountered a stored proc that had RETURN TABLE(...) until seeing this, so it just wasn't being handled correctly.

Your example should work now, though I didn't seek out other examples different from yours to ensure it also handled them -- so please feel free to open another ticket with a failing example if you have more! The fix is included in the latest gem release, v2.2.2.