pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSyntax for cmd to EXEC...how many quotes?
3+ messages / 3 participants
[nested] [flat]
* Syntax for cmd to EXEC...how many quotes?
@ 2004-04-20 23:24 David B <postgresql@thegatelys.com>
2004-04-21 08:44 ` Re: Syntax for cmd to EXEC...how many quotes? denis@coralindia.com
2004-04-21 12:20 ` Re: Syntax for cmd to EXEC...how many quotes? George Weaver <gweaver@shaw.ca>
0 siblings, 2 replies; 3+ messages in thread
From: David B @ 2004-04-20 23:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Syntax for cmd to EXEC...how many quotes?
2004-04-20 23:24 Syntax for cmd to EXEC...how many quotes? David B <postgresql@thegatelys.com>
@ 2004-04-21 08:44 ` denis@coralindia.com
1 sibling, 0 replies; 3+ messages in thread
From: denis@coralindia.com @ 2004-04-21 08:44 UTC (permalink / raw)
To: David B <postgresql@thegatelys.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Syntax for cmd to EXEC...how many quotes?
2004-04-20 23:24 Syntax for cmd to EXEC...how many quotes? David B <postgresql@thegatelys.com>
@ 2004-04-21 12:20 ` George Weaver <gweaver@shaw.ca>
1 sibling, 0 replies; 3+ messages in thread
From: George Weaver @ 2004-04-21 12:20 UTC (permalink / raw)
To: David B <postgresql@thegatelys.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-04-21 12:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-20 23:24 Syntax for cmd to EXEC...how many quotes? David B <postgresql@thegatelys.com>
2004-04-21 08:44 ` denis@coralindia.com
2004-04-21 12:20 ` George Weaver <gweaver@shaw.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox