PostgreSQL 함수 매개 변수로서의 테이블 이름

PostgreSQL 함수 매개 변수로서의 테이블 이름

---

Postgres 함수의 매개 변수로 테이블 이름을 전달하고 싶습니다. 이 코드를 시도했습니다.

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
    BEGIN
    IF EXISTS (select * from quote_ident($1) where quote_ident($1).id=1) THEN
     return 1;
    END IF;
    return 0;
    END;
$$ LANGUAGE plpgsql;

select some_f('table_name');

그리고 나는 이것을 얻었다 :

ERROR:  syntax error at or near "."
LINE 4: ...elect * from quote_ident($1) where quote_ident($1).id=1)...
                                                             ^

********** Error **********

ERROR: syntax error at or near "."

그리고 이것으로 변경했을 때 얻은 오류는 다음과 같습니다 select * from quote_ident($1) tab where tab.id=1.

ERROR:  column tab.id does not exist
LINE 1: ...T EXISTS (select * from quote_ident($1) tab where tab.id...

아마도 내가 얻는 부분이 quote_ident($1)없으면 무언가가 선택되었음을 의미 하기 때문에 작동합니다 . 첫 번째 작업과 두 번째 작업이 동시에 작동하지 않는 이유는 무엇 입니까? 그리고 이것은 어떻게 해결할 수 있습니까?where quote_ident($1).id=11quote_ident($1)

---

이것은 더욱 단순화되고 개선 될 수 있습니다.

CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer) AS
$func$
BEGIN
   EXECUTE format('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)
   INTO result;
END
$func$  LANGUAGE plpgsql;

스키마 규정 이름으로 호출 (아래 참조) :

SELECT some_f('myschema.mytable');  -- would fail with quote_ident()

또는:

SELECT some_f('"my very uncommon table name"');

주요 포인트

• 사용하여 OUT매개 변수 기능을 단순화 할 수 있습니다. 동적 SQL의 결과를 직접 선택하여 수행 할 수 있습니다. 추가 변수 및 코드가 필요하지 않습니다.

• EXISTS원하는 것을 정확히 수행합니다. true행이 존재하는지 여부를 얻습니다 false. 이를 수행하는 다양한 방법이 EXISTS있으며 일반적으로 가장 효율적입니다.

• 정수를 되돌리고 싶은 것 같으므로 boolean결과를에서 EXISTS로 캐스팅하여 integer정확히 얻은 결과 를 얻습니다. 대신 부울 을 반환 합니다.

• 개체 식별자 유형 regclass을에 대한 입력 유형으로 사용합니다 _tbl. 그것은 모든 것을 수행 quote_ident(_tbl)하거나 format('%I', _tbl)할 것입니다.

  • .. SQL 주입도 방지 합니다.

  • .. 테이블 이름이 유효하지 않거나 존재하지 않거나 현재 사용자가 볼 수없는 경우 즉시 실패합니다. ( regclass매개 변수는 기존 테이블 에만 적용됩니다 .)

  • .. 스키마 규정 테이블 이름으로 작동합니다. 여기서 일반 quote_ident(_tbl)또는 format(%I)모호성을 해결할 수 없기 때문에 실패합니다. 스키마와 테이블 이름을 별도로 전달하고 이스케이프해야합니다.

• format()구문을 단순화하고 사용 방법을 보여주기 위해 여전히를 사용 하지만 %s대신 %I. 일반적으로 쿼리는 더 복잡하므로 format()더 많은 도움이됩니다. 간단한 예에서는 다음과 같이 연결할 수 있습니다.

  EXECUTE 'SELECT (EXISTS (SELECT FROM ' || _tbl || ' WHERE id = 1))::int'
  

• 목록에 id하나의 테이블 만있는 동안 열 을 테이블 한정 할 필요가 없습니다 FROM. 이 예에서는 모호성이 없습니다. (동적) 내부 SQL 명령 EXECUTE에는 별도의 범위가 있으며, 함수 변수 또는 매개 변수는 함수 본문의 일반 SQL 명령과 달리 여기에서 표시되지 않습니다.

PostgreSQL 9.1로 테스트되었습니다. format()최소한 해당 버전이 필요합니다.

다음 은 동적 SQL에 대한 사용자 입력을 항상 적절하게 이스케이프하는 이유입니다 .

db <> 여기에서 SQL 주입을 시연합니다.
이전 sqlfiddle .

---

이러지마

그게 답입니다. 끔찍한 안티 패턴입니다. 어떤 용도로 사용됩니까? 클라이언트가 데이터를 원하는 테이블을 알고 있다면 SELECT FROM ThatTable! 이것이 필요한 방식으로 데이터베이스를 설계했다면 아마도 잘못 설계했을 것입니다. 데이터 액세스 계층이 테이블에 값이 있는지 알아야하는 경우 해당 코드에서 동적 SQL 부분을 수행하는 것은 매우 쉽습니다. 데이터베이스로 푸시하는 것은 좋지 않습니다.

아이디어가 있습니다. 원하는 층 수를 입력 할 수있는 엘리베이터 내부에 장치를 설치합시다. 그런 다음 "이동"을 누르면 기계식 손이 원하는 층의 올바른 버튼으로 이동하여 눌러줍니다. 혁명적!

분명히 내 대답이 설명에 너무 짧아서이 결함을 더 자세히 수리하고 있습니다.

나는 조롱 할 생각이 없었다. 내 어리석은 엘리베이터 예제는 질문에서 제안 된 기술의 결함을 간결하게 지적 할 수있는 가장 좋은 장치 였습니다 . 이 기술은 완전히 쓸모없는 간접 계층을 추가하고 강력하고 잘 이해 된 DSL (SQL)을 사용하여 호출자 공간에서 테이블 이름 선택을 모호하고 기괴한 서버 측 SQL 코드를 사용하는 하이브리드로 불필요하게 이동합니다.

쿼리 구성 논리를 동적 SQL로 이동하여 책임을 분할하면 코드를 이해하기가 더 어려워집니다. 오류 가능성이있는 사용자 지정 코드의 이름에서 완벽하게 합리적인 규칙 (SQL 쿼리가 선택할 항목을 선택하는 방법)을 파괴합니다.

• Dynamic SQL offers the possibility of SQL injection that is hard to recognize in the front end code or the back end code singly (one must inspect them together to see this).

• Stored procedures and functions can access resources that the SP/function owner has rights to but the caller doesn't. As far as I understand, when you use code that produces dynamic SQL and runs it, the database executes the dynamic SQL under the rights of the caller. This means you either won't be able to use privileged objects at all, or you have to open them up to all clients, increasing the surface area of potential attack to privileged data. Setting the SP/function at creation time to always run as a particular user (in SQL Server, EXECUTE AS) may solve that problem, but makes things more complicated. This exacerbates the risk of SQL injection mentioned in the previous point, by making the dynamic SQL a very enticing attack vector.

• When a developer must understand what the application code is doing in order to modify it or fix a bug, he'll find it very difficult to get the exact SQL query being executed. SQL profiler can be used, but this takes special privileges and can have negative performance effects on production systems. The executed query can be logged by the SP but this increases complexity for no reason (maintaining new tables, purging old data, etc.) and is totally non-obvious. In fact, some applications are architected such that the developer does not have database credentials, so it becomes almost impossible for him to actually see the query being submitted.

• When an error occurs, such as when you try to select a table that doesn't exist, you'll get a message along the lines of "invalid object name" from the database. That will happen exactly the same whether you're composing the SQL in the back end or the database, but the difference is, some poor developer who's trying to troubleshoot the system has to spelunk one level deeper into yet another cave below the one where the problem actually exists, to dig into the wonder-procedure that Does It All and try to figure out what the problem is. Logs won't show "Error in GetWidget", it will show "Error in OneProcedureToRuleThemAllRunner". This abstraction will just make your system worse.

Here's a far better example in pseudo-C# of switching table names based on a parameter:

string sql = string.Format("SELECT * FROM {0};", EscapeSqlIdentifier(tableName));
results = connection.Execute(sql);

Every flaw I have mentioned with the other technique is completely absent from this example.

There's just no purpose, no benefit, no possible improvement in submitting a table name to a stored procedure.

---

Inside plpgsql code, The EXECUTE statement must be used for queries in which table names or columns come from variables. Also the IF EXISTS (<query>) construct is not allowed when query is dynamically generated.

Here's your function with both problems fixed:

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
DECLARE
 v int;
BEGIN
      EXECUTE 'select 1 FROM ' || quote_ident(param) || ' WHERE '
            || quote_ident(param) || '.id = 1' INTO v;
      IF v THEN return 1; ELSE return 0; END IF;
END;
$$ LANGUAGE plpgsql;

---

The first doesn't actually "work" in the sense that you mean, it works only in so far as it does not generate an error.

Try SELECT * FROM quote_ident('table_that_does_not_exist');, and you will see why your function returns 1: the select is returning a table with one column (named quote_ident) with one row (the variable $1 or in this particular case table_that_does_not_exist).

What you want to do will require dynamic SQL, which is actually the place that the quote_* functions are meant to be used.

---

If the question was to test if the table is empty or not (id=1), here is a simplified version of Erwin's stored proc :

CREATE OR REPLACE FUNCTION isEmpty(tableName text, OUT zeroIfEmpty integer) AS
$func$
BEGIN
EXECUTE format('SELECT COALESCE ((SELECT 1 FROM %s LIMIT 1),0)', tableName)
INTO zeroIfEmpty;
END
$func$ LANGUAGE plpgsql;

---

If you want table name, column name and value to be dynamically passed to function as parameter

use this code

create or replace function total_rows(tbl_name text, column_name text, value int)
returns integer as $total$
declare
total integer;
begin
    EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total;
    return total;
end;
$total$ language plpgsql;


postgres=# select total_rows('tbl_name','column_name',2); --2 is the value

---

I have 9.4 version of PostgreSQL and I always use this code:

CREATE FUNCTION add_new_table(text) RETURNS void AS
$BODY$
begin
    execute
        'CREATE TABLE ' || $1 || '(
        item_1      type,
        item_2      type
        )';
end;
$BODY$
LANGUAGE plpgsql

And then:

SELECT add_new_table('my_table_name');

It works good for me.

Attention! Above example is one of those which shows "How do not if we want to keep safety during querying the database" :P

참고URL : https://stackoverflow.com/questions/10705616/table-name-as-a-postgresql-function-parameter