psql pgadmin Procedure error column "trip" does not exist LINE 7: CALL example2(Trip)-CodePudding

I want to create a stored procedure in pgadmin that will output the number of rows from the "Trip" table. The table itself is output in cmd, the number of rows in this table is also output in cmd. But when writing a procedure and calling it, such an error comes out. I have psql version 15. How can I fix this error?

My code:

CREATE PROCEDURE example2(INOUT _name character varying) AS $$
BEGIN
    SELECT count(*) FROM "_name";
END;
$$ LANGUAGE plpgsql;

CALL example2(Trip)

Error:

ERROR: ERROR: The "trip" column does not exist
LINE 7: CALL example2(Trip)

CodePudding user response：

You are better off using a function then a procedure as it is easier to get value out. Using dynamic SQL along with the format function to properly quote the _name parameter.

CREATE OR REPLACE FUNCTION public.example2(_name character varying)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    ct integer;
BEGIN
    EXECUTE format('SELECT count(*) FROM %I', _name) INTO ct;
    RETURN ct;
END;
$function$


select example2('animals');
 example2 
----------
        8