Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have a custom type

CREATE TYPE mytype as (id uuid, amount numeric(13,4));

I want to pass it to a function with the following signature:

CREATE FUNCTION myschema.myfunction(id uuid, mytypes mytype[])
  RETURNS BOOLEAN AS...

How can I call this in postgres query and inevitably from PHP?

share|improve this question
 
first portion resolved: –  tafaju Aug 17 '12 at 18:49
 
select myschema.myfunc('0d6311cc-0d74-4a32-8cf9-87835651e1ee' , ARRAY[('ac747f0e-93d4-43a9-bc5b-09df06593239', '25.00') , ('6449fb3b-844e-440e-8973-31eb6bbefc81', '10.00')]::mytype[] ); still need php though! –  tafaju Aug 17 '12 at 18:50
 
This went unsolved for a long time. It probably would have helped to add the tag postgresql since that one draws a lot more attention than version-specific tags. –  Erwin Brandstetter Jul 24 '13 at 16:46
add comment

2 Answers

You can use the alternative syntax with a string literal instead of the array constructor, which is a Postgres function and may cause trouble when you need to pass values - like in a prepared statement:

SELECT myschema.myfunc('0d6311cc-0d74-4a32-8cf9-87835651e1ee'
          , '{"(0d6311cc-0d74-4a32-8cf9-87835651e1ee,25)"
             ,"(6449fb3b-844e-440e-8973-31eb6bbefc81,10)"}'::mytype[]);

I added a line break between the two row types in the array for display here. That's legal.

How to find the correct syntax for any literal?

Here is a demo:

CREATE TEMP TABLE mytype (id uuid, amount numeric(13,4));

INSERT INTO mytype VALUES
  ('0d6311cc-0d74-4a32-8cf9-87835651e1ee', 25)
 ,('6449fb3b-844e-440e-8973-31eb6bbefc81', 10);

SELECT ARRAY(SELECT m FROM mytype m);

Returns:

{"(0d6311cc-0d74-4a32-8cf9-87835651e1ee,25.0000)","(6449fb3b-844e-440e-8973-31eb6bbefc81,10.0000)"}

It probably should be noted that any table (including temporary tables) implicitly creates a row type of the same name.

share|improve this answer
add comment
select myschema.myfunc('0d6311cc-0d74-4a32-8cf9-87835651e1ee'
                , ARRAY[('ac747f0e-93d4-43a9-bc5b-09df06593239', '25.00')
                              , ('6449fb3b-844e-440e-8973-31eb6bbefc81', '10.00')]::mytype[]
    );

Still need PHP portion of this resolved though, still not sure how to call a function populating with the custom array parameter.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.