• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1471
  • Last Modified:

RECORD return type for a PostgreSQL 7.4.13 function

hi experts,

can i declare function with return type RECORD in PostgreSQL 7.4.13? I tried it but it doesn't seem to be working. When i try to create the function in pgAdmin a return type of RECORD is not available in the drop-down menu for picking a function return type. And when i try to create it in psql it gave me an error.

Is this possible at all in my version of PostgreSQL or not?

If i can not use a RECORD as the return type of a function what else can I do to make my function return the result of SELECT sql query? Let's say I have a SQL query that queries multiple tables and returns a large number of records. How can I make a PostgreSQL function that returns the results from the query?

Btw, this is for use in a CrystalReport.NET. I am using Visual Studio 2005 and my application is in VB.NET.

Thank you.
0
Lou1
Asked:
Lou1
  • 3
  • 3
  • 2
2 Solutions
 
ivanovnCommented:
According to the Postgres 7.4 documentation, yes there is a RECORD type (http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS)

What is your syntax where you're using the RECORD variable? Could you post a sample piece of code?
0
 
ivanovnCommented:
Oops, I'm sorry, I didn't read that quite well... however the same link works. Look at the comment at the bottom, someone posted an example of a functions with RECORD return type.
0
 
earth man2Commented:
you can define a set returning function to "returns setof TYPE_X" eg

create type type_x( col1 int, col2 text, col3 date );

create function many_x() returns setof type_x as $$
declare
x type x;
begin
  x.col1 = 1;
  x.col2 = 'set returning functions are cool';
  x.col3 = now();
  return next x;
  x.col1 = 2;
  x.col2 = 'if you can get a handle on them';
  return next x;
  return;
end;
$$ language plpgsql;

select * from many_x();


see http://www.experts-exchange.com/Databases/PostgreSQL/Q_21591979.html

create function generate_dates( start_date date, end_date date ) returns setof date as $$
declare
  the_date date := start_date;
begin
  while the_date <= end_date loop
      return next the_date;
      the_date := the_date + 1;
  end loop;
  return;
end;
$$ language plpgsql;

dev=>  select * from generate_dates('01-OCT-2005','03-OCT-2005');
 generate_dates
----------------
 2005-10-01
 2005-10-02
 2005-10-03
(3 rows)


0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Lou1Author Commented:
ivanovn,

i ran into the same portion of PostgreSQL 7.4's documentation in my Internet research, which led me to think it should allow me to declare function with return type RECORD, but alas, I haven't been able to do it so far. Here is the code for the function I want to create, which by the way I came up with after reading the following article:

http://www.postgresql.org/docs/techdocs.10

Code:

CREATE OR REPLACE FUNCTION public.test_function
 RETURNS SETOF RECORD
 AS
'DECLARE r record;
BEGIN
 for r in EXECUTE \'SELECT fname, lname FROM personalinformation WHERE personalinformationid = 1 ;\' loop
     return next r;
 end loop;
 return;
END;'
  LANGUAGE 'plpgsql' STABLE;

Like I said in my first post, I tried to create the function in pgAdmin, where when you create a new function it gives a drop-down menu with all the possible return types for the function and RECORD is not in there. Also, I tried simply running the SQL above in pgAdmin, i.e. bypassing their GUI for function creation, but I got the following error:

ERROR:  syntax error at or near "RETURNS" at character 51

And finally, I put the code above in a .sql file and ran it via psql but I got the exact same error as above.



Earthman,

Yes, I thought about doing that as well, but first I wanted to investigate if it is possible at all to create a function with return type of RECORD in PostgreSQL 7.4. Another thing is that we may be switching to PostgreSQL 8.0 later this month so in that case I will just wait until then because I know for sure you can declare functions with return type RECORD there.
0
 
earth man2Commented:
try

create type person_name ( fname text, lname text );

CREATE OR REPLACE FUNCTION public.test_function RETURNS SETOF person_name AS $$
DECLARE
 r person_name;
BEGIN
 for r in EXECUTE 'SELECT fname, lname FROM personalinformation WHERE personalinformationid = 1 ;' loop
     return next r;
 end loop;
 return;
END;
$$ LANGUAGE 'plpgsql';

0
 
earth man2Commented:
is it a syntax problem ?
CREATE OR REPLACE FUNCTION public.test_function()
0
 
ivanovnCommented:
I don't have 7.4 so it's hard for me to test this. So bear with me while I guess.

Try returning a RECORD instead of SETOF RECORD. For example:

CREATE OR REPLACE FUNCTION public.test_function
 RETURNS RECORD
 AS
'DECLARE r record;
BEGIN
 for r in SELECT fname, lname FROM personalinformation WHERE personalinformationid = 1 ; loop
     return next r;
 end loop;
 return;
END;'
  LANGUAGE 'plpgsql' STABLE;

This is just a guess from looking at the example posted by the individual at the postgres documentation link I provided above. Let us know if that works. Good luck.
0
 
Lou1Author Commented:
duh, all i needed to do was put parenthesis after the function name and that made it work. boy i feel stupid. Here is the code that works just in case somebody wants to see it:

CREATE OR REPLACE FUNCTION public.test_function()
  RETURNS SETOF record AS
'DECLARE r record;
BEGIN for r in EXECUTE \'SELECT fname, lname FROM personalinformation WHERE personalinformationid =1 ;\' loop
      return next r;
end loop;
return;
END;'
  LANGUAGE 'plpgsql' STABLE;

thanks guys, you both helped.

ps: pgAdmin's error messages are not helpful at all. Their IDE is light years behind Visual Studio's IDE.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now