2
CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$
11
SELECT * FROM perl_int(42);
17
CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$
26
SELECT * FROM perl_int(42);
32
CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
35
SELECT perl_set_int(5);
40
SELECT * FROM perl_set_int(5);
45
CREATE OR REPLACE FUNCTION perl_set_int(int) RETURNS SETOF INTEGER AS $$
48
SELECT perl_set_int(5);
59
SELECT * FROM perl_set_int(5);
70
CREATE TYPE testrowperl AS (f1 integer, f2 text, f3 text);
71
CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
80
SELECT * FROM perl_row();
86
CREATE OR REPLACE FUNCTION perl_row() RETURNS testrowperl AS $$
87
return {f2 => 'hello', f1 => 1, f3 => 'world'};
95
SELECT * FROM perl_row();
101
CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
109
SELECT * FROM perl_set();
114
CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
116
{ f1 => 1, f2 => 'Hello', f3 => 'World' },
118
{ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
122
ERROR: elements of Perl result array must be reference to hash
123
SELECT * FROM perl_set();
124
ERROR: elements of Perl result array must be reference to hash
125
CREATE OR REPLACE FUNCTION perl_set() RETURNS SETOF testrowperl AS $$
127
{ f1 => 1, f2 => 'Hello', f3 => 'World' },
128
{ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
129
{ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
134
----------------------
140
SELECT * FROM perl_set();
142
----+-------+------------
144
2 | Hello | PostgreSQL
148
CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$
151
SELECT perl_record();
157
SELECT * FROM perl_record();
158
ERROR: a column definition list is required for functions returning "record"
159
SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text);
165
CREATE OR REPLACE FUNCTION perl_record() RETURNS record AS $$
166
return {f2 => 'hello', f1 => 1, f3 => 'world'};
168
SELECT perl_record();
169
ERROR: function returning record called in context that cannot accept type record
170
SELECT * FROM perl_record();
171
ERROR: a column definition list is required for functions returning "record"
172
SELECT * FROM perl_record() AS (f1 integer, f2 text, f3 text);
178
CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$
181
SELECT perl_record_set();
186
SELECT * FROM perl_record_set();
187
ERROR: a column definition list is required for functions returning "record"
188
SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text);
193
CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$
195
{ f1 => 1, f2 => 'Hello', f3 => 'World' },
197
{ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
200
SELECT perl_record_set();
201
ERROR: function returning record called in context that cannot accept type record
202
SELECT * FROM perl_record_set();
203
ERROR: a column definition list is required for functions returning "record"
204
SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text);
205
ERROR: elements of Perl result array must be reference to hash
206
CREATE OR REPLACE FUNCTION perl_record_set() RETURNS SETOF record AS $$
208
{ f1 => 1, f2 => 'Hello', f3 => 'World' },
209
{ f1 => 2, f2 => 'Hello', f3 => 'PostgreSQL' },
210
{ f1 => 3, f2 => 'Hello', f3 => 'PL/Perl' }
213
SELECT perl_record_set();
214
ERROR: function returning record called in context that cannot accept type record
215
SELECT * FROM perl_record_set();
216
ERROR: a column definition list is required for functions returning "record"
217
SELECT * FROM perl_record_set() AS (f1 integer, f2 text, f3 text);
219
----+-------+------------
221
2 | Hello | PostgreSQL
225
CREATE TYPE footype AS (x INTEGER, y INTEGER);
226
CREATE OR REPLACE FUNCTION foo_good() RETURNS SETOF footype AS $$
232
SELECT * FROM foo_good();
239
CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$
240
return {y => 3, z => 4};
242
SELECT * FROM foo_bad();
243
ERROR: Perl hash contains nonexistent column "z"
244
CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$
247
SELECT * FROM foo_bad();
248
ERROR: composite-returning Perl function must return reference to hash
249
CREATE OR REPLACE FUNCTION foo_bad() RETURNS footype AS $$
255
SELECT * FROM foo_bad();
256
ERROR: composite-returning Perl function must return reference to hash
257
CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$
260
SELECT * FROM foo_set_bad();
261
ERROR: set-returning Perl function must return reference to array
262
CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$
263
return {y => 3, z => 4};
265
SELECT * FROM foo_set_bad();
266
ERROR: set-returning Perl function must return reference to array
267
CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$
273
SELECT * FROM foo_set_bad();
274
ERROR: elements of Perl result array must be reference to hash
275
CREATE OR REPLACE FUNCTION foo_set_bad() RETURNS SETOF footype AS $$
280
SELECT * FROM foo_set_bad();
281
ERROR: Perl hash contains nonexistent column "z"
282
CREATE OR REPLACE FUNCTION perl_get_field(footype, text) RETURNS integer AS $$
283
return $_[0]->{$_[1]};
285
SELECT perl_get_field((11,12), 'x');
291
SELECT perl_get_field((11,12), 'y');
297
SELECT perl_get_field((11,12), 'z');