CODEWITHSUNDEEP
sqlpostgresqlfunctionconcatenationsql-insert
devStudent
devStudent

Reputation: 1

I am trying to figure out why my INSERT INTO code does not accept my Function(rental_string)

The function alone seems to be working. pgAdmin gave me the thumbs up on it, as well as the table. I tried many different ways to type the function and INSERT INTO portion. I have been working on this for over an hour and seem stuck. I was doing okay up until this portion. Any ideas?

CREATE FUNCTION rental_string (title VARCHAR(250), rating VARCHAR(250))
RETURNS VARCHAR(250)
LANGUAGE plpgsql
AS
$$
BEGIN
RETURN CONCAT(title,' - '  ,rating);
END;
$$;


INSERT INTO detailed_table 
    SELECT DISTINCT rental_string(f.title, f.rating), c.name, l.name
    FROM inventory AS i 
    JOIN film as f ON f.film_id = i.film_id
    JOIN film_category as fc ON fc.film_id = f.film_id 
    JOIN category AS c ON c.category_id = fc.category_id
    JOIN language AS l ON l.language_id = f.language_id
    GROUP BY f.title, c.name, l.name
    ORDER BY f.title;


CREATE TABLE detailed_table (
name_rating VARCHAR(250),
category VARCHAR(250),
film_language VARCHAR(250)
);

I keep getting these errors:

ERROR: function rental_string(character varying, unknown, mpaa_rating) does not exist
LINE 3: SELECT DISTINCT rental_string(title, '', rating), c.name, l...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

SQL state: 42883
Character: 47

Upvotes: -1

Views: 56

Answers (1)

Alfin E. R.
Alfin E. R.

Reputation: 928

Your function only accept 2 arguments: title VARCHAR(250), rating VARCHAR(250)

however, your query seemed to pass 3 arguments -- based on the error message. (The SELECT query you provided does seem to be different to the SELECT query shown in the error message) :

SELECT DISTINCT rental_string(title, '', rating), c.name, l...

that seems to be the reason for the error HINT: No function matches the given name and argument types. You might need to add explicit type casts.

double check the query and remove the second argument ('') if any or what causes it. It should solve the problem!

Upvotes: 1

Related Questions