Map Postgres stored procedures and functions

[ErikEJ]

• Get list of sprocs/functions
• Get parameters
• Get result set shape
• Create list of NpgsqlParameter with a map of parameter type to NpgSqlDbType
• Inject mapper interface to procedure scaffolder
• Scaffold DbContext and procedure calls
• Scaffold result sets
• Run some smoke testing
• Quote handling!
• Get someone to test it!

List stored procs and the parameters:

https://dataedo.com/kb/query/postgresql/list-stored-procedures

https://dataedo.com/kb/query/postgresql/list-stored-procedure-parameters

Get result shape:

https://github.com/vb-consulting/NpgsqlRest/blob/master/NpgsqlRest/RoutineSourceQuery.cs

Type mapping:

https://www.npgsql.org/doc/types/basic.html#read-mappings

Data types and aliases:

https://www.postgresql.org/docs/current/datatype.html

[aarondglover]

Yes please. No more executeRawSql for.stored procs pls

[ErikEJ]

Get result set shape and name:

    with cte1 as (
        select
            lower(r.routine_type) as type,
            quote_ident(r.specific_schema) as schema,
            quote_ident(r.routine_name) as name,
            proc.oid as oid,
            r.specific_name,
            des.description as comment,
            proc.proisstrict as is_strict,
            proc.provolatile as volatility_option,
            proc.proretset as returns_set,
            (r.type_udt_schema || '.' || r.type_udt_name)::regtype::text as return_type,

            coalesce(
                array_agg(quote_ident(p.parameter_name::text) order by p.ordinal_position) filter(where p.parameter_mode = 'IN' or p.parameter_mode = 'INOUT'),
                '{}'::text[]
            ) as in_params,

            case when r.data_type = 'USER-DEFINED' then null
            else
                coalesce(
                    (array_agg(quote_ident(p.parameter_name::text) order by p.ordinal_position) 
                    filter(where p.parameter_mode = 'INOUT' or p.parameter_mode = 'OUT')),
                    '{}'::text[]
                )
            end as out_params,

            coalesce(
                array_agg(
                    case when p.data_type = 'bit' then 'varbit' else (p.udt_schema || '.' || p.udt_name)::regtype::text end
                    order by p.ordinal_position
                ) filter(where p.parameter_mode = 'IN' or p.parameter_mode = 'INOUT'),
                '{}'::text[]
            ) as in_param_types,

            case when r.data_type = 'USER-DEFINED' then null
            else
                coalesce(
                    (array_agg(case when p.data_type = 'bit' then 'varbit' else (p.udt_schema || '.' || p.udt_name)::regtype::text end 
                               order by p.ordinal_position) 
                    filter(where p.parameter_mode = 'INOUT' or p.parameter_mode = 'OUT')),
                    '{}'::text[]
                )
            end as out_param_types,

            coalesce(
                array_agg(p.parameter_default order by p.ordinal_position) filter(where p.parameter_mode = 'IN' or p.parameter_mode = 'INOUT'),
                '{}'::text[]
            ) as in_param_defaults,

            case when proc.provariadic <> 0 then true else false end as has_variadic,

            r.type_udt_schema,
            r.type_udt_name,
            r.data_type
        from
            information_schema.routines r
            join pg_catalog.pg_proc proc on r.specific_name = proc.proname || '_' || proc.oid
            left join pg_catalog.pg_description des on proc.oid = des.objoid
            left join information_schema.parameters p on r.specific_name = p.specific_name and r.specific_schema = p.specific_schema

        where
            r.specific_schema = any(
                select
                    nspname
                from
                    pg_catalog.pg_namespace
                where
                    nspname not like 'pg_%'
                    and nspname <> 'information_schema'
            )
            and proc.prokind in ('f', 'p')
            and not lower(r.external_language) = any(array['c', 'internal'])
            and coalesce(r.type_udt_name, '') <> 'trigger'
        group by
            r.routine_type, r.specific_schema, r.routine_name,
            proc.oid, r.specific_name, des.description,
            r.data_type, r.type_udt_schema, r.type_udt_name,
            proc.proisstrict, proc.procost, proc.prorows, proc.proparallel, proc.provolatile,
            proc.proretset, proc.provariadic

    ), cte2 as (

        select 
            cte1.schema, 
            cte1.specific_name,
            array_agg(quote_ident(col.column_name) order by col.ordinal_position) as out_params,
            array_agg(
                case when col.data_type = 'bit' then 'varbit' else (col.udt_schema || '.' || col.udt_name)::regtype::text end
                order by col.ordinal_position
            ) as out_param_types
        from 
            information_schema.columns col
            join cte1 on 
            cte1.data_type = 'USER-DEFINED' and col.table_schema = cte1.type_udt_schema and col.table_name = cte1.type_udt_name 
        group by
           cte1.schema, cte1.specific_name

    )
    select
        type,
        cte1.schema,
        cte1.name,
        is_strict,
        volatility_option,

        returns_set,
        coalesce(return_type, 'void') as return_type,

        coalesce(array_length(coalesce(cte1.out_params, cte2.out_params), 1), 1) as return_record_count,
        case 
            when array_length(coalesce(cte1.out_params, cte2.out_params), 1) is null 
            then array[cte1.name]::text[] 
            else coalesce(cte1.out_params, cte2.out_params) 
        end as return_record_names,

        case 
            when array_length(coalesce(cte1.out_param_types, cte2.out_param_types), 1) is null 
            then array[return_type]::text[] 
            else coalesce(cte1.out_param_types, cte2.out_param_types)
        end as return_record_types,

        coalesce(cte1.out_params, cte2.out_params) = '{}' as is_unnamed_record

    from cte1
    left join cte2 on cte1.schema = cte2.schema and cte1.specific_name = cte2.specific_name

[ErikEJ]

@roji Would you be willing to have a quick glance at the generated code once I get a smoke test up an running here (just for sanity check by an "expert") ? 🙏

[roji]

Of course! The coming couple of weeks are pretty easy (am going to be traveling again), but definitely ping me and I'll do my best.

[ErikEJ]

@roji (or anyone else) - I am trying to run the Northwind.sql script from the EF Core provider tests, but unable to do so, maybe because it contains the GO keyword - I am trying via both Query Tool and PSQL Tool in "pgadmin 4" 🍜

[ErikEJ]

@roji - solved - replaced GO with ;

[ErikEJ]

@roji (or others) - I am stuck calling a result returning function with the EF Core 6 provider:

            var npgsqlParameters = new []
            {
                new NpgsqlParameter
                {
                    ParameterName = null,
                    Value = CustomerID ?? Convert.DBNull,
                    NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Varchar,
                },
            };

            var _ = await _context.Set<CustOrderHistResult>().FromSqlRaw("SELECT \"public\".\"CustOrderHist\" ($1)", npgsqlParameters).ToListAsync(cancellationToken);

I get this error: : '08P01: bind message supplies 0 parameters, but prepared statement "" requires 1'

[ErikEJ]

@roji I changed to using this, was unable to use positional parameters apparently ?

 var npgsqlParameters = new []
 {
     new NpgsqlParameter
     {
         ParameterName = "CustomerID",
         Value = CustomerID ?? Convert.DBNull,
         NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Char,
     },
 };
 var _ = await _context.SqlQueryAsync<CustOrderHistResult>("SELECT * FROM \"public\".\"CustOrderHist\" (@CustomerID)", npgsqlParameters, cancellationToken);

 return _;

[ErikEJ]

This feature has now been implemented, supporting SQL Functions returning sets - will take it from there.

I implemented a fix for this in the latest daily build, would be grateful if you could try it out.

A sample smoke test project with examples of the generated code is available here

@roji Would be very grateful if you could take a quick look

[roji]

@ErikEJ can you point me at the specific file you want me to look at?

Re positional parameters, they're indeed not yet supported in EF (see dotnet/efcore#27377), even though they're supported at the Npgsql ADO.NET level (and are the proper way to do things with PG).

[ErikEJ]

@roji This file: https://github.com/ErikEJ/EFCorePowerTools/blob/master/test/ScaffoldingTester/PostgresTester/Models/NorthwindContextProcedures.cs

And thanks, that explains why I could not make it work 😆

[roji]

OK thanks. Here are a couple notes:

• PG has both stored functions and stored procedures - they're two different things. Functions can be embedded anywhere in any query (like any function), and invoking them in isolation is done via SELECT * FROM func(). Procedures, on the other hand, are invoked via CALL proc(), and cannot be embedded in a larger SQL query (much like SQL Server sprocs); they can also do various things which functions cannot, like control transactions. So at the very least from a naming perspective, what you have in that file are stored functions, not stored procedures. In an ideal world you'd know how to reverse engineer both :)
• The PG provider doesn't generate quotes around identifiers unless they're needed (see this), so it doesn't quote e.g. public (that's not needed). Of course, you can absolutely quote it in your reverse-engineering, just saying.
• Regardless, I'd recommend using C# raw string literals (""") to avoid needing to escape double-quotes (unless you need to support old C# versions etc.)

Otherwise looks great!

[ErikEJ]

@roji Thanks, immensly appreciated.

So at the very least from a naming perspective, what you have in that file are stored functions, not stored procedures.

Yeah, as I understand it they are set returning functions - and that is what I have in scope at the moment (and agree about naming, but you know what the say about it)

Are you saying that there are other ways to use/map set returning functions with EF Core?

I support C# with .NET 6, so unsure about raw string literals?

[roji]

If the user's goal is to map an existing SRF, then you're doing it the right way (SELECT * FROM ...). But it's likely that in many (most?) cases users would actually want to map stored procedures, in which case you would generate CALL instead. Of course, it's totally reasonable to only support SRFs as a first step, but I'd at least make sure they're documented/called "functions" and not use the word "procedure" anywhere (since those are invoked with CALL).

I support C# with .NET 6, so unsure about raw string literals?

Right, unless you can vary the generated C# based on the project's C# version you can't (but that's a bit of a shame :)).

[ErikEJ]

@roji Thanks again. I will most likely only map different function types for postgres then. I assume scalar functions will be simply mapped as DbFunctions?

[ErikEJ]

@roji I am mapping everything that returns a result set, and will look a actual stored procedures and will try to come up with better naming.

[ErikEJ]

But first I will await feedback from some early adopters...

[roji]

Yeah, scalar functions are just regular functions, like in SQL Server.