Skip site navigation (1) Skip section navigation (2)

Re: Feature Request for Debugging SQL in PGAdmin3 when SQL contains variables

From: Raymond O'Donnell <rod(at)iol(dot)ie>
To: Dan Shoubridge <dan(dot)shoubridge(at)autovhc(dot)co(dot)uk>
Cc: 'Paragon Corporation' <lr(at)pcorp(dot)us>, pgadmin-support(at)postgresql(dot)org
Subject: Re: Feature Request for Debugging SQL in PGAdmin3 when SQL contains variables
Date: 2010-11-17 11:32:29
Message-ID: 4CE3BD4D.4050101@iol.ie (view raw or flat)
Thread:
Lists: pgadmin-support
On 17/11/2010 11:22, Dan Shoubridge wrote:
>>> Dan,
>>> Ok, I understand and it's a shame the vars aren't strong typed. It's
> little things like that that make me want SQL Server back. It doesn't seem
> like a big thing to some people, but for a lot of developers the amount of
> work added by this when debugging sql adds up over time.
>
>> I know it's a new invention in PostgreSQL 9.0 so might not be an option for
> you, but wouldn't the new
>
>> DO command do what you are looking for?
>
> pgScript looks better for me atm, as you can have @ to specify variables, it
> doesn't let you in anonymous code blocks - this defeats the object of
> efficient debugging entirely (I'd have to remove the : from the sql than add
> it again afterwards)
>
> I tried the following:
>
> DO language plpgsql $$
> DECLARE x integer;
> BEGIN
> 	x := 7;
> 	SELECT * FROM site WHERE sitecode = x;
>   END
>   $$;
>
> but got the error
>
> 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 7 at SQL statement
>
> ********** Error **********
>
> ERROR: query has no destination for result data
> SQL state: 42601
> Hint: If you want to discard the results of a SELECT, use PERFORM instead.
> Context: PL/pgSQL function "inline_code_block" line 7 at SQL statement
>
> It looks like I need to declare the returning type, or specify to output to
> the dataoutput window? But the documentation doesn't really expand on this
> scenario, and there isn't any examples I could find through Google.

I haven't been following this thread, but on the above, yes, you do have 
to declare a variable for the returned row, something like:

declare
   x integer;
   rec record;
begin
   x := 7;
   select * into rec from site where sitecode = x;
end;

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

In response to

pgadmin-support by date

Next:From: Mark_SloanDate: 2010-11-17 15:43:00
Subject: pgAdmin 1.12.1 issues talking with master node of a SR-ROHS setup.
Previous:From: Dan ShoubridgeDate: 2010-11-17 11:22:29
Subject: Re: Feature Request for Debugging SQL in PGAdmin3 when SQL contains variables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group