Views are implemented as table plus rewrite rule in Postgres. Details in the manual, chapter Views and the Rule System.
The tricky part: not the view itself depends on involved functions, only the rewrite rule does. So this query should be your solution:
SELECT r.ev_class::regclass AS view, d.refobjid::regprocedure AS function
FROM pg_rewrite r
JOIN pg_depend d ON d.objid = r.oid
AND d.refclassid = 'pg_proc'::regclass -- only functions
WHERE r.ev_class = 'v123'::regclass; -- name of view here (optionally schema-qualified)
Returns all user-defined functions (excluding built-in functions).
The query assumes you did not define any additional rules for the view. Else filter the one with rulename = '_RETURN'
, which is the default name for the rewrite rules of a view.
The cast to regprocedure
(not just regproc
) returns the function with argument types, uniquely identifying it. The name is schema-qualified automatically if the current search_path
would not resolve differently otherwise.
I would never use these objects. They simply don't seem that useful. That said, PostgreSQL does have a method of passing a table to a function via jsonb
. Likely that would be the ideal solution for you if you're looking to get the functionality described in Passing a Table-Valued Parameter to a Stored Procedure. All you'd have to do is map the DataTable to JSON, and then pass the JSON to the function.
To convert a Datatable to JSON you can use JSON.net. See also these Stack Overflow answers.
If the Datatable is too big to serialize to JSON, you may have to create a Foreign Data Wrapper around something that exports the DataRow objects, this would start you down the path mentioned in "Streaming Rows with a DataReader". Though if you don't need a server/client model, you can always dump the DataTable to CSV and read it with a Foreign Data Wrapper: to go down that route check out file-fdw.
I actually tried this. I ran into a ton of problems.
- It wasn't even easy figuring out how to install a package with nuGet,
- Having installed nuGet, despite being the #1 package on nuGet, it doesn't yet work with .NET Core 2.x
- Installing the beta version of nuGet, I got it to work.
Converting a DataTable to JSON with .NET Core 2.x,
using System;
using System.Data;
using Newtonsoft.Json;
class Program
{
static void Main()
{
// Get the DataTable.
DataTable table = GetTable();
// ... Use the DataTable here with SQL.
string json = JsonConvert.SerializeObject(table, Formatting.Indented);
Console.WriteLine(json);
}
static DataTable GetTable()
{
// Here we create a DataTable with four columns.
DataTable table = new DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
// Here we add five DataRows.
table.Rows.Add(25, "Indocin", "David", DateTime.Now);
table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
return table;
}
}
You'd still have to connect to the db and drop the json into an INSERT
statement, but that should be easily done.
cmd.CommandText = "SELECT * FROM myfunc(@json)";
cmd.Parameters.AddWithValue("json", json);
Define myfunc(jsonb)
on the server, and you should be good: you've passed a DataTable
into PostgreSQL.
Best Answer
It is possible: