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

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

From: George Weaver <gweaver(at)shaw(dot)ca>
To: David B <postgresql(at)thegatelys(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Syntax for cmd to EXEC...how many quotes?
Date: 2004-04-21 12:20:20
Message-ID: 000601c4279b$04c12f00$6400a8c0@Dell4500 (view raw or flat)
Thread:
Lists: 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(at)thegatelys(dot)com>
To: <pgsql-sql(at)postgresql(dot)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
>



In response to

Responses

pgsql-sql by date

Next:From: ogjunk-pgjedanDate: 2004-04-21 12:20:52
Subject: Re: Order by YYYY MM DD in reverse chrono order trouble
Previous:From: denisDate: 2004-04-21 11:36:42
Subject: Re: Order by YYYY MM DD in reverse chrono order trouble

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