Postgres Pro
Downloads
Home   >   mailing lists

Thread: Syntax for cmd to EXEC...how many quotes?

Syntax for cmd to EXEC...how many quotes?

From
"David B"
Date:
Folks,

This is driving me crazy...I'm sure it's possible but that I am getting the
#quotes wrong in some way...
I keep getting unterminated string errors...now matter how many quotes I
use.

I have a FN that I want to loop through all views and populate a table with
a count(*) from each views.

To do it I'm doing a LOOP around all views...something like:

FOR r_rec IN SELECT viewname from pg_views
LOOP
   sql_string := 'INSERT INTO temp_table ( view_name, row_count ) SELECT
''' || r_rec.viewname || ''', count(*) FROM ' || r_rec.viewname  || ' ; ' ;
   EXEC sql_string ;

END LOOP ;

END ;


Building that sql_string is the problem.
Any thoughts folks?

-D
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.648 / Virus Database: 415 - Release Date: 3/31/2004

Re: Syntax for cmd to EXEC...how many quotes?

From
George Weaver
Date:
David,

I tend to use \ to escape things like ' - I find it makes it somewhat easier
to debug.

What about:

sql_string :=\' INSERT INTO temp_table ( view_name, row_count ) SELECT \'                       || r_rec.viewname  ||
                  \', count(*) FROM \'                       ||  r_rec.viewname  ||                       \' ; \'    ;
 

HTH

George

----- Original Message ----- 
From: "David B" <postgresql@thegatelys.com>
To: <pgsql-sql@postgresql.org>
Sent: Tuesday, April 20, 2004 6:24 PM
Subject: [SQL] Syntax for cmd to EXEC...how many quotes?


> Folks,
>
> This is driving me crazy...I'm sure it's possible but that I am getting
the
> #quotes wrong in some way...
> I keep getting unterminated string errors...now matter how many quotes I
> use.
>
> I have a FN that I want to loop through all views and populate a table
with
> a count(*) from each views.
>
> To do it I'm doing a LOOP around all views...something like:
>
> FOR r_rec IN SELECT viewname from pg_views
> LOOP
>
>     sql_string := 'INSERT INTO temp_table ( view_name, row_count ) SELECT
> ''' || r_rec.viewname || ''', count(*) FROM ' || r_rec.viewname  || ' ; '
;
>
>     EXEC sql_string ;
>
> END LOOP ;
>
> END ;
>
>
> Building that sql_string is the problem.
> Any thoughts folks?
>
> -D
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.648 / Virus Database: 415 - Release Date: 3/31/2004
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

datediff script

From
"Erik Aronesty"
Date:
This is a not-quite complete implementation of the SY/MS sql datediff.  The
months_between function can be extrapolated from it as well.  I looked for
it on forums, etc. and all I found were people complaining about the lack of
an example.  Please post fixes/changes or a link to a better one... if you
know of it.

CREATE OR REPLACE FUNCTION public.datediff(varchar, timestamp, timestamp) RETURNS int4 AS
'
DECLAREarg_mode alias for $1;arg_d2 alias for $2;arg_d1 alias for $3;
BEGIN

if arg_mode = \'dd\' or arg_mode = \'d\' or arg_mode = \'y\' or arg_mode =
\'dy\' or arg_mode = \'w\' thenreturn cast(arg_d1 as date) - cast(arg_d2 as date);
elsif arg_mode = \'ww\' then       return ceil( ( cast(arg_d1 as date) - cast(arg_d2 as date) ) / 7.0);
elsif arg_mode = \'mm\' OR arg_mode = \'m\' thenreturn 12 * (date_part(\'year\',arg_d1) - date_part(\'year\',arg_d2))
 + date_part(\'month\',arg_d1) - date_part(\'month\',arg_d2)            + case when date_part(\'day\',arg_d1) >
 
date_part(\'day\',arg_d2)                   then 0                   when date_part(\'day\',arg_d1) =
date_part(\'day\',arg_d2) and cast(arg_d1 as time) >= cast(arg_d2 as time)                   then 0
else-1              end;
 
elsif arg_mode = \'yy\' OR arg_mode = \'y\' OR arg_mode = \'yyyy\' thenreturn (cast(arg_d1 as date) - cast(arg_d2 as
date))/ 365;
 
end if;

END;
' LANGUAGE 'plpgsql' VOLATILE;

Re: Syntax for cmd to EXEC...how many quotes?

From
denis@coralindia.com
Date:
Try (to solve string terminating error ):
   sql_string := ''INSERT INTO temp_table ( view_name, row_count ) SELECT
'' || r_rec.viewname || '', count(*) FROM '' || r_rec.viewname  || '' ; '' ;

BUT, you will be needing to put view_name in Quote too... try it yourself...

HTH

Denis

----- Original Message -----
From: David B <postgresql@thegatelys.com>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, April 21, 2004 4:54 AM
Subject: [SQL] Syntax for cmd to EXEC...how many quotes?


> Folks,
>
> This is driving me crazy...I'm sure it's possible but that I am getting
the
> #quotes wrong in some way...
> I keep getting unterminated string errors...now matter how many quotes I
> use.
>
> I have a FN that I want to loop through all views and populate a table
with
> a count(*) from each views.
>
> To do it I'm doing a LOOP around all views...something like:
>
> FOR r_rec IN SELECT viewname from pg_views
> LOOP
>
>     sql_string := 'INSERT INTO temp_table ( view_name, row_count ) SELECT
> ''' || r_rec.viewname || ''', count(*) FROM ' || r_rec.viewname  || ' ; '
;
>
>     EXEC sql_string ;
>
> END LOOP ;
>
> END ;
>
>
> Building that sql_string is the problem.
> Any thoughts folks?
>
> -D
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.648 / Virus Database: 415 - Release Date: 3/31/2004
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org