Hi,
I suspect that this question probably relates more to SQL in
general, but I think it arises more often when used with
placeholders.
When I want to compare data in the database to external ref-
erences, I often use patterns like:
| $DB->do ('CREATE TEMPORARY TABLE tmpData (ID INT PRIMARY KEY);') or die
($DB->errstr ());
| $DB->do ('INSERT INTO tmpData (ID) VALUES (1), (2), (6), (7);') or die
($DB->errstr ());
| my $s = $DB->prepare ('SELECT t1.ID AS SurplusID, t2.ID AS MissingID ' .
| 'FROM tmpData AS t1 ' .
| 'FULL OUTER JOIN (SELECT unnest(:Data::INT[])) AS t2
(ID) ' .
| 'USING (ID) ' .
| 'WHERE t1.ID IS NULL OR t2.ID IS NULL;') or die
($DB->errstr ());
| my @Data = (1, 4, 7);
| $s->bind_param (':Data', \@Data) or die ($DB->errstr ());
| $s->execute () or die ($DB->errstr ());
How can I pass the reference data to PostgreSQL if it isn't
a simple array, but for example tuples of numbers and
strings? In SQL, I would use something like:
| CREATE TEMPORARY TABLE tmpData (ID INT PRIMARY KEY, Name TEXT NOT NULL);
| INSERT INTO tmpData (ID, Name) VALUES (1, 'Z'), (2, 'D'), (6, 'H'), (7, 'B');
| SELECT t1.ID AS SurplusID,
| t1.Name AS SurplusName,
| t2.ID AS MissingID,
| t2.Name AS MissingName
| FROM tmpData AS t1
| FULL OUTER JOIN (VALUES (1, 'Z'), (4, 'E'), (7, 'L'))
| AS t2 (ID, Name)
| USING (ID, Name)
| WHERE t1.ID IS NULL OR t2.ID IS NULL;
But I don't know how to pass such a structure via DBD::Pg.
Is there a way without defining a composite type?
TIA,
Tim
