다양한 SELECT 쿼리의 결과를 반환하는 PL / pgSQL의 함수 리팩토링

나는 잘 텍스트 형태로 형성되는 PostgreSQL의 SELECT 쿼리를 출력하는 기능을 썼다. 지금은 텍스트 더 이상 출력하지 않지만, 실제로 데이터베이스에 대해 생성 된 SELECT 문을 실행하고 결과를 반환 - 단지 자체는 것 쿼리처럼.

CREATE OR REPLACE FUNCTION data_of(integer)
  RETURNS text AS
$BODY$
DECLARE
   sensors varchar(100);   -- holds list of column names
   type    varchar(100);   -- holds name of table
   result  text;           -- holds SQL query
       -- declare more variables

BEGIN
      -- do some crazy stuff

      result := 'SELECT\r\nDatahora,' || sensors ||
      '\r\n\r\nFROM\r\n' || type ||
      '\r\n\r\nWHERE\r\id=' || $1 ||'\r\n\r\nORDER BY Datahora;';

      RETURN result;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION data_of(integer) OWNER TO postgres;

센서는 테이블 타입 열 이름의리스트를 보유하고있다. 이러한 선언과 함수의 과정에서 채워진다. 결국, 그들은 값을 같이 개최 :

기본 테이블의 정의.

변수 센서 유형에 해당 테이블에 대해 여기에 표시된 모든 열을 개최한다. 예를 들어 : 유형 인 경우 pcdmet 다음 센서가 될 것 'datahora, dirvento, precipitacao, pressaoatm, radsolacum, tempar, umidrel, velvento'

변수는 결과에 저장되어있는 SELECT 문을 작성하는 데 사용됩니다. 처럼:

SELECT Datahora, column1, column2, column3
FROM   myTable
WHERE  id=20
ORDER  BY Datahora;

지금, 내 기능은 텍스트로이 문을 반환합니다. 나는 붙여 복사 및 pgAdmin 또는 psql를 통해 그것을 실행합니다. 나는이 자동화 자동으로 쿼리를 실행하고 결과를 반환합니다. 내가 어떻게 할 수 있습니까?

해결법

==============================
1.(나는 마지막 읽기 계속에 가장 적합한 저장!) 당신은 동적 SQL을 실행합니다. 교장에, EXECUTE의 도움으로 plpgsql에서 그의 간단한. 당신은 커서를 필요가 없습니다 - 사실, 대부분의 시간은 당신이 더 떨어져 명시 적 커서 않고 있습니다. 검색과 SO에 대한 예제를 찾을 수 있습니다.

(나는 마지막 읽기 계속에 가장 적합한 저장!) 당신은 동적 SQL을 실행합니다. 교장에, EXECUTE의 도움으로 plpgsql에서 그의 간단한. 당신은 커서를 필요가 없습니다 - 사실, 대부분의 시간은 당신이 더 떨어져 명시 적 커서 않고 있습니다. 검색과 SO에 대한 예제를 찾을 수 있습니다.

아직 정의되지 않은 유형의 레코드를 반환 할 : 당신으로 실행 문제. 함수는 (OUT 또는 INOUT 파라미터 또는) 반품 절 리턴 타입을 선언해야한다. 귀하의 경우에 당신은 번호, 이름과 반환 열 유형이 다양하기 때문에, 익명의 기록으로 후퇴 할 것입니다. 처럼:
```
CREATE FUNCTION data_of(integer)
  RETURNS SETOF record AS ...
```
그러나이 특히 유용하지 않습니다. 이 방법은 함수의 모든 호출에 열 정의 목록을 제공해야 할 것이다. 처럼:
```
SELECT * FROM data_of(17)
AS foo (colum_name1 integer
      , colum_name2 text
      , colum_name3 real);
```
사전에 열을 모를 때 그러나 당신은 어떻게도, 이런 짓을 했을까? 당신은 JSON, jsonb, hstore 또는 XML과 같은 덜 구조화 된 문서 데이터의 유형에 의존 수 :

그러나이 질문의 목적을 위해의 당신은 가능한 한 개인, 올바르게 입력 및 명명 된 열을 반환한다고 가정하자.

열 datahora 내가 데이터 유형 타임 스탬프를 가정하고 다양한 이름과 데이터 유형이 더 많은 열이 항상 있다는 것을거야, A가 주어진 것 같다.

이름은 우리가 반환 형식의 일반적인 이름에 찬성 포기 것이다. 유형 우리도 포기하고, 모든 데이터 유형이 텍스트에 캐스팅 될 수 있기 때문에 텍스트에 대한 모든 캐스팅 것입니다.
```
CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, col2 text, col3 text) AS
$func$
DECLARE
   _sensors text := 'col1::text, col2::text';  -- cast each col to text
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE '
      SELECT datahora, ' || _sensors || '
      FROM   ' || quote_ident(_type) || '
      WHERE  id = $1
      ORDER  BY datahora'
   USING  _id;

END
$func$ LANGUAGE plpgsql;
```
버전 9.1 이상 당신은 더 단순화하기 위해 형식을 ()를 사용할 수 있습니다 :
```
RETURN QUERY EXECUTE format('
   SELECT datahora, %s  -- identifier passed as unescaped string
   FROM   %I            -- assuming the name is provided by user
   WHERE  id = $1
   ORDER  BY datahora'
  ,_sensors, _type)
USING  _id;
```
또, 각각의 열 이름이 제대로 탈출 할 수 있고 깨끗한 방법이 될 것입니다.

당신의 반환 형식은이 같은 질문 업데이트 후에 본다

우리는 값의 변수 번호를 저장할 수있는이 경우 배열 유형에 함수 I 리조트의 반환 유형을 정의해야한다. 당신이 너무 결과에서 이름을 구문 분석 할 수 있도록 또한, 나는 열 이름을 가진 배열을 반환 :
```
CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, names text[], values float8[] ) AS
$func$
DECLARE
   _sensors text := 'col1, col2, col3';  -- plain list of column names
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT datahora
           , string_to_array($1)  -- AS names
           , ARRAY[%s]            -- AS values
      FROM   %s
      WHERE  id = $2
      ORDER  BY datahora'
    , _sensors, _type)
   USING  _sensors, _id;
END
$func$  LANGUAGE plpgsql;
```
실제로 링크 된 페이지에있는 테이블의 인스턴스 하나 (테이블의 모든 열을 반환하려는 경우,이 간단한, 다형성 유형의 매우 강력한 솔루션을 사용 :
```
CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int)
  RETURNS SETOF anyelement AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s  -- pg_typeof returns regtype, quoted automatically
      WHERE  id = $1
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$ LANGUAGE plpgsql;
```
요구:
```
SELECT * FROM data_of(NULL::pcdmet, 17);
```
다른 테이블 이름으로 호출 pcdmet 교체합니다.

==============================

2.당신은 아마 커서를 반환 할 수 있습니다. 이 같은 것을 (나는 그것을 시도하지 않은) 시도 :

당신은 아마 커서를 반환 할 수 있습니다. 이 같은 것을 (나는 그것을 시도하지 않은) 시도 :

CREATE OR REPLACE FUNCTION data_of(integer)
  RETURNS refcursor AS
$BODY$
DECLARE
      --Declaring variables
      ref refcursor;
BEGIN
      -- make sure `sensors`, `type`, $1 variable has valid value
      OPEN ref FOR 'SELECT Datahora,' || sensors ||
      ' FROM ' || type ||
      ' WHERE nomepcd=' || $1 ||' ORDER BY Datahora;';
      RETURN ref;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION data_of(integer) OWNER TO postgres;

==============================

3.내가 말할 죄송하지만 질문은 매우 명확하지 않다. 그러나 당신은 함수가 반환하는 커서 변수를 생성하고 사용하는 방법 자체 포함 된 예를 찾을 수 아래. 희망이 도움이!

내가 말할 죄송하지만 질문은 매우 명확하지 않다. 그러나 당신은 함수가 반환하는 커서 변수를 생성하고 사용하는 방법 자체 포함 된 예를 찾을 수 아래. 희망이 도움이!

begin;

create table test (id serial, data1 text, data2 text);

insert into test(data1, data2) values('one', 'un');
insert into test(data1, data2) values('two', 'deux');
insert into test(data1, data2) values('three', 'trois');

create function generate_query(query_name refcursor, columns text[])
returns refcursor 
as $$
begin
  open query_name for execute 
    'select id, ' || array_to_string(columns, ',') || ' from test order by id';
  return query_name;
end;
$$ language plpgsql;

select generate_query('english', array['data1']);
fetch all in english;

select generate_query('french', array['data2']);
fetch all in french;
move absolute 0 from french; -- do it again !
fetch all in french;

select generate_query('all_langs', array['data1','data2']);
fetch all in all_langs;

-- this will raise in runtime as there is no data3 column in the test table
select generate_query('broken', array['data3']);

rollback;

==============================

4.

# copy paste me into bash shell directly
clear; IFS='' read -r -d '' sql_code << 'EOF_SQL_CODE'
CREATE OR REPLACE FUNCTION func_get_all_users_roles()
  -- define the return type of the result set as table
  -- those datatypes must match the ones in the src
  RETURNS TABLE (
                 id           bigint
               , email        varchar(200)
               , password     varchar(200)
               , roles        varchar(100)) AS
$func$
BEGIN
   RETURN QUERY 
   -- start the select clause
   SELECT users.id, users.email, users.password, roles.name as roles
   FROM user_roles
   LEFT JOIN roles ON (roles.guid = user_roles.roles_guid)
   LEFT JOIN users ON (users.guid = user_roles.users_guid)
   -- stop the select clause
;
END
$func$  LANGUAGE plpgsql;
EOF_SQL_CODE
# create the function
psql -d db_name -c "$sql_code"; 

# call the function 
psql -d db_name -c "select * from func_get_all_users_roles() "

from https://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어느 / 빠른 최고? SELECT * 또는 SELECT 컬럼 1, colum2, 3 열, 등 (0)	2020.03.08
[SQL] 는 SQL Server의 기능에 값을 쉼표로 가득한 VARCHAR 구분 전달 (0)	2020.03.08
[SQL] SQLite는 - UPSERT 하지 INSERT 또는 REPLACE (0)	2020.03.08
[SQL] 외래 키 제약은 사이클 또는 여러 개의 캐스케이드 경로의 원인? (0)	2020.03.08
[SQL] MySQL의에서 누적 합계 열 만들기 (1)	2020.03.08

복붙노트

[SQL] 다양한 SELECT 쿼리의 결과를 반환하는 PL / pgSQL의 함수 리팩토링

다양한 SELECT 쿼리의 결과를 반환하는 PL / pgSQL의 함수 리팩토링

해결법

2.당신은 아마 커서를 반환 할 수 있습니다. 이 같은 것을 (나는 그것을 시도하지 않은) 시도 :

3.내가 말할 죄송하지만 질문은 매우 명확하지 않다. 그러나 당신은 함수가 반환하는 커서 변수를 생성하고 사용하는 방법 자체 포함 된 예를 찾을 수 아래. 희망이 도움이!

4.

'SQL' 카테고리의 다른 글

티스토리툴바