Postgres PL/pgSQL, possible to declare anonymous custom types?

Question

With DB2 I'm able to declare anonymous custom types (e.g. row types or composite types) for my user defined functions - see the following example (especially the last line):

DB2 example:

CREATE OR REPLACE FUNCTION myFunction(IN input1 DECIMAL(5), IN input2 DECIMAL(5)) 
    RETURNS DECIMAL(2) 
    READS SQL DATA 
    LANGUAGE SQL 
    NO EXTERNAL ACTION 
    NOT DETERMINISTIC
BEGIN
  DECLARE TYPE customAnonymousType AS ROW(a1 DECIMAL(2), a2 DECIMAL(2), a3 DECIMAL(2));
  /* do something fancy... */

Can I do something similar with PL/pgSQL? I know I would be able to use existing row types, also existing user defined types - but do I really have to define the type in advance?

I also know about the RECORD type, but as far as I understand I would not be able to use it in arrays (and also it would not be a well defined type).

Comments asked for an example, even though it does lengthen the question a lot I tried to define a quite simple example (still for DB2):

CREATE OR REPLACE FUNCTION myFunction(IN input1 DECIMAL(5), IN input2 DECIMAL(5)) 
    RETURNS DECIMAL(2) 
    READS SQL DATA 
    LANGUAGE SQL 
    NO EXTERNAL ACTION 
    NOT DETERMINISTIC
BEGIN
  DECLARE TYPE customAnonymousType AS ROW(a1 DECIMAL(2), a2 CHARACTER VARYING(50));
  DECLARE TYPE customArray AS customAnonymousType ARRAY[INTEGER];

  DECLARE myArray customArray;

  SET myArray[input1] = (50, 'Product 1');
  SET myArray[input2] = (99, 'Product 2');

  RETURN myArray[ARRAY_FIRST(myArray)].a1;
END

This function of course only serves as a dummy function (but I suppose it is already quite long for a question here). Actually it just decides which number to return depending on if input1 is greater than input2. If input1 is smaller than input2, it returns 50, if input2 is smaller or equal than input2 it would return 99.

I know I'm not even using my a2 character field of my type (so in this case I would also be able to just use an number array) and that there are probably many, many better solutions to return two fixed numbers depending on the input values, but still my original questions remains if I am able to use anonymous custom types in PL/pgSQL (as I would in Oracle or DB2 procedures) - or if there are any similar alternatives.

Can you provide a more worked-out example of what you would like to do with the anonymous type? There is most likely a solution in PL/pgSQL but it might lie in some other direction. — Patrick
– Patrick, Commented Jan 19, 2016 at 8:25
Actually I want to store them in an array, so the next line for DB2 would look like DECLARE TYPE myArray AS customAnonymousType ARRAY[INTEGER]. The array is used for intermediate calculations. — Matthias
– Matthias, Commented Jan 19, 2016 at 8:34
In the absence of that example you should still post: Simply declare a variable of type int[]. In PG arrays are dynamic so they grow with your data needs. (While array support in PG is much better than in other DBMSs, there is still much left to desire for.) But again, perhaps you are better off with some other solution. — Patrick
– Patrick, Commented Jan 19, 2016 at 9:14
Thanks for the first solution, unfortunately this would only be possible if all types my composite type is composed of are numbers. If the type would look like DECLARE TYPE customAnonymousType AS ROW(a1 DECIMAL(2), a2 CHARACTER VARYING(50)); this would not be possible anymore. — Matthias
– Matthias, Commented Jan 19, 2016 at 9:39
In instead of asking for a translation from DB2 to Postgresql explain what is the real problem. And the real problem is not the implementation but the desired input and output. — Clodoaldo Neto
– Clodoaldo Neto, Commented Jan 19, 2016 at 11:55

Pavel Stehule · Accepted Answer · 2016-01-19 16:25:34Z

2

You cannot to create types with local visibility in Postgres. This functionality is not supported. Postgres support global custom composite types only.

See CREATE TYPE doc. This statement cannot be used in DECLARE part of plpgsql block.

answered Jan 19, 2016 at 16:25

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres PL/pgSQL, possible to declare anonymous custom types?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related