×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Trying to write a function

Trying to write a function

Trying to write a function

(OP)
I have a table called "movie" and I would like to bring up the last X number of them with this function but it  gives me error:

ERROR:  return type mismatch in function declared to return text
DETAIL:  Final SELECT must return exactly one column.
CONTEXT:  SQL function "last_movie"

here is what it looks like :

create FUNCTION last_movie(int) returns setof movie                            
as 'SELECT m.movie_id, m.movie_name, m.kids
   FROM movie m
  WHERE m.movie_id > (( SELECT max(movie.movie_id) - $1
           FROM movie));'              
language SQl;

if I change the "m.movie_id, m.movie_name, m.kids" to "*" then it works but I would like just those particular fields in the results.

Any ideas? Thanks

RE: Trying to write a function

I read:
DETAIL:  Final SELECT must return exactly one column.

So you could cat those three columns together - which might not be that useful.

SELECT * ... works, because a row is a type - a movie-type in your case.

Create an own type for your purpose:

CODE


CREATE TYPE movie_triple AS (id int, name varchar, kids int);
create FUNCTION last_movie(int) returns setof movie_triple
as 'SELECT m.movie_id, m.movie_name, ...

don't visit my homepage: http://home.arcor.de/hirnstrom/bewerbung

RE: Trying to write a function

(OP)
Stefanwagner you are brilliant!

That worked with a slight mod, thank you very much.

CREATE TYPE movie_triple AS (id int, name varchar, kids varchar);
create FUNCTION last_movie(int) returns setof movie_triple                             
as 'SELECT m.movie_id, m.movie_name, m.kids
   FROM movie m
  WHERE m.movie_id > (( SELECT max(movie.movie_id) - $1
           FROM movie));'              
language SQl;

RE: Trying to write a function

Hi

Here on Tek-Tips we used to thank for the received help by giving stars. Please click the

* Thank stefanwagner
for this valuable post!


link at the bottom of stefanwagner's post. That way you both show your gratitude and indicate this thread as helpful.

Feherke.
http://rootshell.be/~feherke/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close