PostgreSQL: No function matches the given name and argument types

Question

I have the following procedure:

DROP FUNCTION presAdress();

CREATE FUNCTION presadress() RETURNS VARCHAR(100) AS $$
DECLARE studioName text;
    BEGIN

    RETURN (
    SELECT address AS pres_address
    FROM MovieExec
    WHERE cert# IN (
        SELECT presC# 
        FROM Studio
        WHERE name = studioName)
        );
    END;

$$ LANGUAGE plpgsql;

I try to run the procedure:

select presadress('Paramount');

But I get the following error message:

ERROR: function presadress(text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 294

I suspect that this is because there is some kind of error regarding the in parameters of the procedure, but I have been unable to find a solution.

You have declared the function to take no arguments. Hence, when you pass an argument, the function has no definition. — Gordon Linoff
– Gordon Linoff, Commented Sep 21, 2015 at 17:39
So how do I reformulate it to take in a string of arbitrary length? I thought this was done in the 'DECLARE studioName text;' line. — Sahand
– Sahand, Commented Sep 21, 2015 at 17:44

Community · Accepted Answer · 2017-05-23 11:46:46Z

1

Use a function parameter, like @Gordon demonstrates, but you don't need plpgsql for this at all. And the query can simplified (shorter, faster):

CREATE FUNCTION presadress(_studioname text)
  RETURNS text AS
$$
SELECT m.address
FROM   studio s
JOIN   movieexec m ON m.cert# = s.presc#
WHERE  s.name = _studioname
$$ LANGUAGE sql STABLE;

Function volatility can be STABLE.

CommunityBot

11 silver badge

answered Sep 22, 2015 at 3:31

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2015-09-21 17:50:33Z

0

I think you want a declaration more like this:

CREATE FUNCTION presadress (v_studioName text)
    RETURNS VARCHAR(100) AS $$
BEGIN
    RETURN(SELECT address AS pres_address
           FROM MovieExec
           WHERE cert# IN (SELECT presC# 
                           FROM Studio
                           WHERE name = v_studioName)
         );
END;
$$ LANGUAGE plpgsql;

answered Sep 21, 2015 at 17:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: No function matches the given name and argument types

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related