时间戳

首页 > 解决方案 > 通过在 postgresql 上声明变量来选择查询

postgresql - 通过在 postgresql 上声明变量来选择查询

问题描述

我是新工作的 postgresql 和 pgadmin4。我写了一个非常简单的查询。

我有一个名为PgFinalLocationsTable公共模式的表。在我的表中有几个字段。其中一个字段是UserName。我想声明一个变量，最后根据这个变量在我的表上进行选择，如下所示：

DO $$
DECLARE myvar text default 'sa';
BEGIN
    select * from public."PgFinalLocationsTable" where "UserName" = myvar;
END $$;

但为什么我收到这些消息：

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 4 at SQL statement
SQL state: 42601

很简单的查询！！！

在谷歌搜索并查看堆栈上的帖子后，我更改了我的查询，如下所示：

CREATE OR REPLACE FUNCTION fun(text myvar) RETURNS text AS $$
--DECLARE myvar text;
BEGIN
    select * from public."PgFinalLocationsTable" where "UserName" = myvar;
END;
$$ language plpgsql;

select fun('sa');

我想返回我所有的字段，我不想使用plpgsql。我想使用 PostgreSQL。无论如何，我收到了这个错误：

ERROR:  type myvar does not exist
SQL state: 42704

我的第一个查询和第二个查询有什么问题？当我想传递一个变量时，我应该为选择查询创建一个函数吗？

我做所有事情是因为我想创建这个 sql 查询：

"IF (NOT EXISTS(SELECT 1 FROM [dbo].[{0}] WHERE [UserId] = @UserId And [DeviceId] = @DeviceId)) " +
"BEGIN " +
"INSERT INTO [dbo].[{0}]([Id], [Location], [Timestamp], [UserId], [DeviceId], [AllowDomains], [Topic], [UserName], [FirstName], [LastName], [JobLocationName], [LocationId], [AppVersion], [AppName]) " +
"VALUES(@Id, GEOGRAPHY::Point(@X, @Y, 4326), @Timestamp, @UserId, @DeviceId, @AllowDomains, @Topic, @UserName, @FirstName, @LastName, @JobLocationName,  @LocationId,  @AppVersion, @AppName) " +
"END "

标签： postgresqlpostgis

解决方案

你不懂DO指挥。DOcommand 是没有声明的匿名函数，并且因为它没有声明输出，所以除了调试流之外不可能有任何其他结果。

所以你的第一个例子在 PostgreSQL 中没有意义。MSSQL 中取消绑定查询的结果作为 MS SQL 过程的结果返回。在 PostgreSQL 中不可能有类似的事情。PostgreSQL 只知道可以返回标量值、复合值或关系（只有一个）的函数。当你来自 MS SQL 时，尽你所能，试着忘记几乎所有来自 MS SQL 的知识。

ERROR:  type myvar does not exist
SQL state: 42704

这个错误很干净——你切换变量名和类型名——真正的类型 myvar 不存在。

一些返回表的函数可能如下所示：

CREATE OR REPLACE FUNCTION fx1(myvar text)
RETURNS SETOF public."PgFinalLocationsTable" AS $$
BEGIN
  RETURN QUERY SELECT * FROM public."PgFinalLocationsTable" WHERE "UserName" = myvar;
END;
$$ LANGUAGE plpgsql;

或者您只能使用 SQL 语言

CREATE OR REPLACE FUNCTION fx1(myvar text)
RETURNS SETOF public."PgFinalLocationsTable" AS $$
  SELECT * FROM public."PgFinalLocationsTable" WHERE "UserName" = $1;
$$ LANGUAGE sql;

因为 PostgreSQL 不支持取消绑定查询，所以不允许它。您应该使用RETURN QUERY命令 - 在 PLpgSQL 语言中。

因为使用存储过程编程在 PostgreSQL 和 MSSQL 之间确实不同（MSSQL 与其他任何不同），请尝试阅读文档 - 这还不错https://www.postgresql.org/docs/current/static/plpgsql .html

您的函数可以在 Postgres 中看起来像（我不知道使用过的类型）

CREATE OR REPLACE FUNCTION fx("_UserId" int,
                              "_DeviceId" int,
                              "_X" int,
                              "_Y" int, 
                              ...
BEGIN
  IF NOT EXISTS(SELECT * FROM /* I don't know what [{0}] means */
                 WHERE "UserId" = "_UserId" AND "DeviceId" = "_DeviceId")
  THEN
    INSERT INTO ..
  END IF;
END;
$$ LANGUAGE plpgsql;

可能您的片段可以通过INSERT INTO ON CONFLICT DO NOTHING命令https://www.postgresql.org/docs/current/static/sql-insert.html在没有程序扩展的情况下解决- 什么更好。

注意 - 使用区分大小写的标识符是通往地狱的捷径。

推荐阅读