Trying to write a function 1

[thewhistler1]

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

 

[stefanwagner]

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:

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

 

[thewhistler1]

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;

 

[feherke]

Hi

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

* [navy]Thank stefanwagner
for this valuable post![/navy]

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/