pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PL/pgSQL Syntax Problem
6+ messages / 5 participants
[nested] [flat]

* PL/pgSQL Syntax Problem
@ 2002-03-04 20:34  Hubert Palme <hubert.palme@web.de>
  0 siblings, 4 replies; 6+ messages in thread

From: Hubert Palme @ 2002-03-04 20:34 UTC (permalink / raw)
  To: pgsql-general@postgresql.org.pgsql-interfaces

Hi,

this function:

CREATE FUNCTION t_nummern(integer) -- Parameter: lfd_nr
RETURNS text AS '
  DECLARE
    lfd_nr ALIAS FOR $1;
    erg text;
    daten record;
  BEGIN
    erg := '''';
    FOR daten IN 
      SELECT * INTO daten FROM telefon WHERE t_lfd_nr = lfd_nr
    LOOP
      IF erg <> '''' THEN
        erg := erg || '', '';
      END IF;
      IF daten.t_einrichtg <> '''' THEN
        erg := erg || daten.t_einrichtg || '': '';
      END IF;
      erg := erg || ''('' || daten.vorwahl || '') '' || daten.telefon_nr;
    END LOOP;
    RETURN erg;
  END;'
LANGUAGE 'plpgsql';

causes the following error:

mitglieder=> select t_nummern(330);
ERROR:  query "SELECT  * INTO daten FROM telefon WHERE t_lfd_nr =  $1 " 
isn't a
SELECT
mitglieder=>

Why isn't this a SELECT??

Thanks in advance,

-- 
Hubert Palme                           Am Walde 1
                                 42119 Wuppertal
<hubert.palme@web.de>



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [INTERFACES] PL/pgSQL Syntax Problem
@ 2002-03-04 23:03  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Hubert Palme <hubert.palme@web.de>
  3 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2002-03-04 23:03 UTC (permalink / raw)
  To: Hubert Palme <hubert.palme@web.de>; +Cc: pgsql-general@postgreSQL.org

Hubert Palme <hubert.palme@web.de> writes:
> ERROR:  query "SELECT  * INTO daten FROM telefon WHERE t_lfd_nr =  $1 " 
> isn't a
> SELECT

> Why isn't this a SELECT??

Get rid of the INTO ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PL/pgSQL Syntax Problem
@ 2002-03-05 15:49  Ashley Clark <aclark@ghoti.org>
  parent: Hubert Palme <hubert.palme@web.de>
  3 siblings, 0 replies; 6+ messages in thread

From: Ashley Clark @ 2002-03-05 15:49 UTC (permalink / raw)
  To: Hubert Palme <hubert.palme@web.de>; +Cc: pgsql-interfaces@postgresql.org

* Hubert Palme in "PL/pgSQL Syntax Problem" dated 2002/03/04 21:34 wrote:

> Hi,

Hi,

>     FOR daten IN 
>       SELECT * INTO daten FROM telefon WHERE t_lfd_nr = lfd_nr

when using a for loop you don't need the into, eg.

FOR daten IN
  SELECT * FROM telefon WHERE t_lfd_nr = lfd_nr

each row will be placed in daten.


-- 
alkyl arches 

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PL/pgSQL Syntax Problem
@ 2002-03-05 20:33  Hubert Palme <hubert.palme@web.de>
  parent: Hubert Palme <hubert.palme@web.de>
  3 siblings, 0 replies; 6+ messages in thread

From: hubert.palme@web.de @ 2002-03-05 20:33 UTC (permalink / raw)
  To: Jan Wieck <janwieck@yahoo.com>; +Cc: Hubert Palme <hubert.palme@web.de>; pgsql-general@postgresql.org; pgsql-interfaces@postgresql.org; pgsql-sql

Thanks to all who answered -- you solved my problemn :-)

Hubert

 > Hubert Palme wrote:
 >> Hi,
 >> 
 >> this function:
 >> 
 >> CREATE FUNCTION t_nummern(integer) -- Parameter: lfd_nr
 >> RETURNS text AS '
 >> DECLARE
 >> lfd_nr ALIAS FOR $1;
 >> erg text;
 >> daten record;
 >> BEGIN
 >> erg := '''';
 >> FOR daten IN
 >> SELECT * INTO daten FROM telefon WHERE t_lfd_nr = lfd_nr
 >> LOOP
 >> IF erg <> '''' THEN
 >> erg := erg || '', '';
 >> END IF;
 >> IF daten.t_einrichtg <> '''' THEN
 >> erg := erg || daten.t_einrichtg || '': '';
 >> END IF;
 >> erg := erg || ''('' || daten.vorwahl || '') '' || daten.telefon_nr;
 >> END LOOP;
 >> RETURN erg;
 >> END;'
 >> LANGUAGE 'plpgsql';
 >> 
 >> causes the following error:
 >> 
 >> mitglieder=> select t_nummern(330);
 >> ERROR:  query "SELECT  * INTO daten FROM telefon WHERE t_lfd_nr =  $1 "
 >> isn't a
 >> SELECT
 >> mitglieder=>
 >> 
 >> Why isn't this a SELECT??

-- 
Hubert Palme                           Am Walde 1
                                 42119 Wuppertal
<hubert.palme@web.de>



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PL/pgSQL Syntax Problem
@ 2002-03-07 16:18  daq <daq@ugyvitelszolgaltato.hu>
  parent: Hubert Palme <hubert.palme@web.de>
  3 siblings, 0 replies; 6+ messages in thread

From: daq @ 2002-03-07 16:18 UTC (permalink / raw)
  To: pgsql-sql

The syntax is:
SELECT INTO daten * FROM telefon WHERE t_lfd_nr = lfd_nr

DAQ





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PL/pgSQL Syntax Problem
@ 2002-03-08 09:40  rolf.ostvik@axxessit.no
  0 siblings, 0 replies; 6+ messages in thread

From: rolf.ostvik@axxessit.no @ 2002-03-08 09:40 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; pgsql-interfaces@postgresql.org; pgsql-sql

On 2002-03-04 21:34 Hubert Palme <hubert.palme@web.de> wrote :

>CREATE FUNCTION t_nummern(integer) -- Parameter: lfd_nr
>RETURNS text AS '
>  DECLARE
>    lfd_nr ALIAS FOR $1;
>    erg text;
>    daten record;
>  BEGIN
>    erg := '''';
>    FOR daten IN 
>      SELECT * INTO daten FROM telefon WHERE t_lfd_nr = lfd_nr
Remove this     ^^^^^^^^^^
so that the "FOR ... SELECT" is

    FOR daten IN 
      SELECT * FROM telefon WHERE t_lfd_nr = lfd_nr

>    LOOP
>      IF erg <> '''' THEN
>        erg := erg || '', '';
>      END IF;
>      IF daten.t_einrichtg <> '''' THEN
>        erg := erg || daten.t_einrichtg || '': '';
>      END IF;
>      erg := erg || ''('' || daten.vorwahl || '') '' || daten.telefon_nr;
>    END LOOP;
>    RETURN erg;
>  END;'
>LANGUAGE 'plpgsql';

regards
-- 
Rolf



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2002-03-08 09:40 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-04 20:34 PL/pgSQL Syntax Problem Hubert Palme <hubert.palme@web.de>
2002-03-04 23:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-05 15:49 ` Ashley Clark <aclark@ghoti.org>
2002-03-05 20:33 ` Hubert Palme <hubert.palme@web.de>
2002-03-07 16:18 ` daq <daq@ugyvitelszolgaltato.hu>
2002-03-08 09:40 Re: PL/pgSQL Syntax Problem rolf.ostvik@axxessit.no

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