当SQL包含变量时，在pgAdmin中调试SQL [英] Debug SQL in pgAdmin when SQL contains variables

问题描述

在SQL Server中，我可以将sql代码从应用程序中复制并粘贴到SSMS中，声明和分配存在于sql中并运行的vars。很好的调试方案。

例如。 （请注意我生锈了，语法可能不正确）：

 声明@x为varchar（10）
 set @x ='abc'
 select * from sometable where somefield = @x 
  

我想在pgAdmin（或另一个postgres工具，任何建议？）中做Postgres类似的操作，我可以把我的SQL（params& all）放入可以对Postgres DB运行的东西。

我意识到你可以创建pgscript，但它看起来不是很好，例如，如果我等同于上述，它不会将单引号围绕在@x中的值，也不会是否让我将它们翻倍，你不会在后面找到一个表 - 只有文本...

目前我有一段SQL有人写的有3个独特的变量，每次使用大约6次...

所以问题是其他人如何有效地调试SQL 最好与我的SQL Server日子类似。

解决方案

您可以使用PREPARE，EXECUTE，DEALLOCATE命令来处理语句，这正是我们正在谈论的。 >

例如：

  PREPARE测试AS SELECT * FROM users WHERE first_name = $ 1 ; 
 EXECUTE test（'paul'）; 
 DEALLOCATE测试; 
  

也许不是像一些可能喜欢的图形，但肯定可行。

In SQL Server I could copy sql code out of an application and paste it into SSMS, declare & assign vars that exist in the sql and run. yay great debugging scenario.

E.g. (please note I am rusty and syntax may be incorrect):

declare @x as varchar(10)
set @x = 'abc'
select * from sometable where somefield = @x

I want to do something similar with Postgres in pgAdmin (or another postgres tool, any recommendations?) where I can just drop my SQL (params & all) into something that will run against Postgres DB.

I realise you can create pgscript, but it doesn't appear to be very good, for example, if I do the equivalent of above, it doesn't put the single quotes around the value in @x, nor does it let me by doubling them up and you don't get a table out after - only text...

Currently I have a piece of SQL someone has written that has 3 unique variables in it which are used around 6 times each...

So the question is how do other people debug SQL efficiently, preferably in a similar fashion to my SQL Server days.

解决方案

You can achieve this using the PREPARE, EXECUTE, DEALLOCATE commands for handling statements, which is really what we are talking about here.

For example:

PREPARE test AS SELECT * FROM users WHERE first_name = $1;
EXECUTE test ('paul');
DEALLOCATE test;

Perhaps not as graphical as some may like, but certainly workable.

这篇关于当SQL包含变量时，在pgAdmin中调试SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！