Solved

RECORD return type for a PostgreSQL 7.4.13 function

Posted on 2006-11-30
8
1,401 Views
Last Modified: 2008-01-09
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
Comment
Question by:Lou1
  • 3
  • 3
  • 2
8 Comments
 
LVL 10

Assisted Solution

by:ivanovn
ivanovn earned 210 total points
Comment Utility
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
 
LVL 10

Expert Comment

by:ivanovn
Comment Utility
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
 
LVL 22

Expert Comment

by:earth man2
Comment Utility
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
 

Author Comment

by:Lou1
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 22

Expert Comment

by:earth man2
Comment Utility
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
 
LVL 22

Accepted Solution

by:
earth man2 earned 210 total points
Comment Utility
is it a syntax problem ?
CREATE OR REPLACE FUNCTION public.test_function()
0
 
LVL 10

Expert Comment

by:ivanovn
Comment Utility
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
 

Author Comment

by:Lou1
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now