0

I'm using PostgreSQL 9.6 (can move to 12 if needed).

I have many schemes with same table_name, so I want to pass scheme_name to functions like this:

CREATE FUNCTION public.test()
RETURNS TABLE(base_name text, slow real)
    LANGUAGE plpgsql
    AS $$
BEGIN
RETURN QUERY 
    SELECT * FROM scheme_name.bases, scheme_name.servers
    WHERE scheme_name.bases.id = scheme_name.servers.id;
END
$$;

1 Answer 1

0

Answer:

  DROP FUNCTION IF EXISTS public.test;
    CREATE FUNCTION public.test(scheme_name character varying)
    RETURNS TABLE(base_name text, scheme_name text)
        LANGUAGE plpgsql
        AS $$
    BEGIN
    RETURN QUERY EXECUTE
        'SELECT ' ||scheme_name||'.bases.base_name, ' ||scheme_name||'.servers.scheme_name FROM ' ||param||'.bases,'||param||'.servers'
        ' WHERE ' ||scheme_name||'.stations.id = ' ||scheme_name||'.servers.id';
    $$;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.