Is it possible to avoid explicit casts for composite types in plpgsql functions?

Question

I am developing a framework that dynamically creates tables for contents storage on PostgreSQL 9.1. One of the API functions allows caller to save a new contents entry by specifying all fields within a given object (say, web form). In order to receive a set of fields framework creates a composite type.

Consider the following code:

CREATE SEQUENCE seq_contents MINVALUE 10000;
CREATE TABLE contents (
    content_id      int8        not null,
    is_edited       boolean     not null default false,
    is_published    boolean     not null default false,
    "Input1"        varchar(60),
    "CheckBox1"     int2,
    "TheBox"        varchar(60),
    "Slider1"       varchar(60)
);
CREATE TYPE "contentsType" AS (
    "Input1"        varchar(60),
    "CheckBox1"     int2,
    "TheBox"        varchar(60),
    "Slider1"       varchar(60)
);
CREATE OR REPLACE FUNCTION push(in_all anyelement) RETURNS int8 AS $push$
DECLARE
    _c_id   int8;
BEGIN
    SELECT nextval('seq_contents') INTO _c_id;

    EXECUTE $$INSERT INTO contents
    SELECT a.*, b.*
      FROM (SELECT $1, true, false) AS a,
           (SELECT $2.*) AS b$$ USING _c_id, in_all;

    RETURN _c_id;
END;
$push$ LANGUAGE plpgsql;

Now, in order to call this function I have to add explicit cast, like this:

SELECT push(('input1',1,'thebox','slider1')::"contentsType");

Is there a way to avoid explicit cast? As I would like external callers not to deal with casts, i.e. hide the logic behind the PostgreSQL functions. Currently I have such error:

SELECT push(('input1',1,'thebox','slider1'));
ERROR:  PL/pgSQL functions cannot accept type record
CONTEXT:  compilation of PL/pgSQL function "push" near line 1

Answer 1

Have you considered passing the record variable as its text representation? In theory, every record variable can be cast to and from text with the normal CAST operator.

Here is the function modified so that in_all has type text and gets casted to "contentsType" in the USING clause:

CREATE OR REPLACE FUNCTION push(in_all text) RETURNS int8 AS $push$
DECLARE
    _c_id   int8;
BEGIN
    SELECT nextval('seq_contents') INTO _c_id;

    EXECUTE $$INSERT INTO contents
    SELECT a.*, b.*
      FROM (SELECT $1, true, false) AS a,
           (SELECT $2.*) AS b$$ USING _c_id, in_all::"contentsType";

    RETURN _c_id;
END;
$push$ LANGUAGE plpgsql;

Then it can be called like this (no explicit reference to the type)

select push( '(input1,1,thebox,slider1)' );

or like that (explicit record casted to text)

SELECT push(('input1',1,'thebox','slider1')::"contentsType"::text);

That would work not just with "contentsType", but any other record type, assuming the function is able to convert it back to that type.

Also in plpgsql, I assume this should work as well:

   ret := push(r::text);

when r is a record variable.

Answer 2

Since you're hard-coding the table name into which you want to insert, and you have a fixed number and type of parameters it needs, I'm not clear on why you need the "contentsType" type at all. Why not eliminate the extra level of parentheses from the function calling, and just pass the four parameters directly? That keeps everything simpler.

CREATE OR REPLACE FUNCTION push(
    "Input1"        varchar(60),
    "CheckBox1"     int2,
    "TheBox"        varchar(60),
    "Slider1"       varchar(60)
) RETURNS int8 AS $push$
DECLARE
    _c_id   int8;
BEGIN
    SELECT nextval('seq_contents') INTO _c_id;

    EXECUTE $$INSERT INTO contents
    VALUES ($1, true, false, $2, %3, %4, $5)
      $$ USING _c_id, "Input1", "CheckBox1", "TheBox", "Slider1");

    RETURN _c_id;
END;
$push$ LANGUAGE plpgsql;

That makes calling the function look like this:

SELECT push('input1',1,'thebox','slider1');

If you're looking to generalized the push() function so that it works for all tables, you'll hit other problems if you get past this one. You won't be able to get past the fact that the function will need to know the table name during execution. If you want to overload the function so that you can have a separate push() for each record type, you need to provide information on the record type somehow. So, if you're looking to do something like this, the short answer to your question is "No."

On the other hand, you may be making this a little harder than it needs to be. I hope you are aware that there is automatically a type created for every table, by the same name as the table. You could probably leverage that to both avoid declaring the type explicitly and to pass a record with the same name as your table -- with dummy entries for the values that the function will fill. I think you could make one totally generic push function, although it might be hard to get past the strong typing issues in plpgsql; writing the function in C might be easier if you're familiar with it.