Question 237123 in PostgreSQL Question posted in
The official documentation can be found here.

Ensuring that a SELECT statement has a WHERE clause filtering on specific columns in Postgresql

Is it possible to force a SELECT statement on a table to include columns from that table in a WHERE clause, in Postgres?

For example, given a table T with columns c1 and c2 can I force a SELECT on table T to include a WHERE clause on column c1.

So that this statement would return some data:

SELECT * from T WHERE c1 = 'MyColumnValue'

But this statement would return no data or an error:

SELECT * from T

I understand that a user could simply include wildcards or the like to circumvent any strictures I might be trying to implement. This is an academic inquery.

Tags:

2

Answers


  1. 0

    Question is:

    can I force a SELECT on table T to include a WHERE clause on column c1.

    Have a look at row security policies.

    Though, a simpler option – I don’t know if it suits your needs – would be to create a view which already restricts rows that can be returned from that table, e.g.

    CREATE OR REPLACE VIEW v_t AS
SELECT * FRom T WHERE c1 = 'MyColumnValue'

    You’d, of course, have to select from v_t view instead of the t table.

    Login or Signup to reply.
  2. 0

    you can use this

    CREATE OR REPLACE FUNCTION get_data()
RETURNS SETOF your_table AS
$$
BEGIN
RETURN QUERY
SELECT *
FROM your_table
WHERE c1 = 'MyColumnValue';
END;
$$
LANGUAGE plpgsql;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search