How to use variables Inside create view inside Procedure?

Question

I have a case where i want to create a view inside a procedure.

Works fine (Usage of variables)

CREATE OR REPLACE PROCEDURE createViewForAnalysis(analysis INT)
LANGUAGE plpgsql
AS
$$
BEGIN
    Perform
        code,
        isReport,
        description,
        SUM(dialoge) as dialoge,
        SUM(response_time) as response_time,
            SUM(response_time)/NULLIF(SUM(dialoge),0) as avg_response_time
    FROM codes_joined WHERE analyse_id=analysis
    GROUP BY code, isReport ,description;

END;
$$;

Also works fine (create view with fixed value in where)

CREATE OR REPLACE PROCEDURE createViewForAnalysis(analysis INT)
LANGUAGE plpgsql
AS
$$
BEGIN
    CREATE OR REPLACE VIEW tcodes_aggregated AS
    SELECT
        code,
        isReport,
        description,
        SUM(dialoge) as dialoge,
        SUM(response_time) as response_time,
        SUM(response_time)/NULLIF(SUM(dialoge),0) as avg_response_time
    FROM codes_joined AS pg WHERE analyse_id=1
    GROUP BY code, isReport ,description;

END;
$$;

When I want to use a variable inside a create view it doesn't take the value inside analysis. It throws "[42703] ERROR: column "analysis" does not exist"

CREATE OR REPLACE PROCEDURE createViewForAnalysis(analysis INT)
LANGUAGE plpgsql
AS
$$
BEGIN
    CREATE OR REPLACE VIEW tcodes_aggregated AS
    SELECT
        code,
        isReport,
        beschreibung,
        SUM(dialoge) as dialoge,
        SUM(response_time) as response_time,
        SUM(response_time)/NULLIF(SUM(dialoge),0) as avg_response_time
    FROM codes_joined AS pg WHERE analyse_id=analysis
    GROUP BY code, isReport ,description;

END;
$$;

Any hints?

I find this interesting. As I understand, the crux is, how to pass the variable into the create view statement with "WHERE analyse_id=1". Answers provide different approach to get similar results - but why can't the parameter be used as in "insert into table values (analysis)" for example? — Bjarni Ragnarsson
– Bjarni Ragnarsson, Commented Dec 23, 2019 at 11:27

user330315 · Accepted Answer · 2019-12-23 09:19:55Z

1

Views can't have parameters or variables. I think what you want is a SQL function that returns the result of the query:

CREATE OR REPLACE function view_for_analysis(analysis INT)
  returns table (code integer, isreport boolean, beschreibung text, dialoge bigint, response_time bigint, avg_response_time numeric)
AS
$$
SELECT
    code,
    isReport,
    beschreibung,
    SUM(dialoge) as dialoge,
    SUM(response_time) as response_time,
    SUM(response_time)/NULLIF(SUM(dialoge),0) as avg_response_time
FROM codes_joined AS pg 
WHERE analyse_id = analysis
GROUP BY code, isReport ,description;
$$;
language SQL;

Note that I guessed the data types of the columns. You will have to adjust the returns table() part to the actual data types of your table.

Then you use it like this:

select *
from view_for_analysis(42);

answered Dec 23, 2019 at 9:19

user330315

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

Comments

kthy · Accepted Answer · 2019-12-23 09:38:12Z

1

Views can't have parameters, but you can create views dynamically using dynamic commands.

CREATE OR REPLACE PROCEDURE createViewForAnalysis(analysis INT)
LANGUAGE plpgsql
AS
$$
BEGIN
    EXECUTE 'CREATE OR REPLACE VIEW tcodes_aggregated AS
        SELECT
            code,
            isReport,
            beschreibung,
            SUM(dialoge) as dialoge,
            SUM(response_time) as response_time,
            SUM(response_time)/NULLIF(SUM(dialoge),0) as avg_response_time
        FROM codes_joined AS pg WHERE analyse_id=' || analysis ||
        'GROUP BY code, isReport, description;';

END;
$$;

answered Dec 23, 2019 at 9:38

kthy

9152 gold badges10 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to use variables Inside create view inside Procedure?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related