VIEW instead of FUNCTION for inserting data [closed]

Question

I have a separate schema called "api" where all my "front-end" functions reside. My API server only have access to this schema. This works great for getting information out of the database in a controlled way.

To insert data in a controlled way I can either create functions in the api schema. But functions with many parameters (i.e. all fields to be inserted) are cumbersome to work with because:

• It's easy to mix up parameters in calls

• I don't know of any syntax to call functions with named parameters

So, I'm thinking of creating "proxy-views" in my api schema instead. The views would show no data (i.e. WHERE false). The API server would do ordinary inserts into these views and a trigger would take care of the actual insert logic.

The code for inserting data with INSERT statements are more readable than calling functions with a list of unnamed parameters.

• Have I missed anything?
• Is this a bad idea?
• Maybe there is someway to name the input parameters for functions?

Thanks!

Answer 1

Named parameters should actually be passed using => not = and not :=.

e.g.

select my_function(argument_one => 42, argument_five => 'foobar');

The => is defined by the SQL standard for named parameters, the := was used historically by Postgres, but its use is discouraged in newer versions in favor of the standard compliant =>

Using Named Notation in the manual.

Answer 2

I just found the reason for me thinking there where no "named-parameter-calling-concept".

I had tried to name the function parameters with =, but apparently I need to use :=.

This answers my question, but it is unfortunate because = and := should be completely interchangeable in Postgres. Now I know that this is not the case.

EDIT ---

After reading up on this I came to the conclusion that "=" equals ":=" only in plpgsql. If calling a function is done in SQL I suppose that this is not true, hence the ":=" is needed. I'd better start using := everywhere.