Tabellenname, Feldname als Funktionsparameter?

Hallo,

wieso kann ich in folgender Funktion die Parameter nicht im SQL-Ausdruck verwenden?

CREATE OR REPLACE FUNCTION Test(pmTableName text, pmColumnName text)
RETURNS d_boolean AS
$BODY$BEGIN
PERFORM pmColumnName FROM pmTableName;
RETURN TRUE;

END;$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;

ich bekomme die folgende Fehlermeldung:

ERROR: syntax error at or near “$2”
SQL Status:42601
Kontext:SQL statement in PL/PgSQL function “test” near line 3

Was mache ich falsch?


MfG,
CUBa42

Du benutzt nicht EXECUTE dafür. Das solltest Du aber, wenn Du dynamische Statements hast.

Andreas

Oh, Danke - das ging aber flott!

Ich hatte als Beispiel nur folgendes in der Hilfe gefunden:

CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE plpgsql;

War wohl nicht Äquivalent genug.

Aber blöde Frage, wie setzte ich Execute nun für diese Aufgabenstellung ein? (Ich hatte gehofft, dass man mir ein simples Beispiel liefert…)

Nun habe ich folgendes deklariert:

CREATE OR REPLACE FUNCTION Test(pmTableName text, pmColumnName text)
RETURNS d_boolean AS
$BODY$BEGIN
Prepare X(text, text) AS SELECT $2 FROM $1;
Execute X(pmTableName, pmColumnName);
RETURN TRUE;

END;$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;

Das passiert aber auch nicht den Syntaxchecker ohne Fehler.
Hat vielleicht jemand ein einfaches Beispiel zur Hand, oder einen Link.
Die Hilfe war mir leider hierzu noch keine Hilfe.

Wozu benötigst Du eine Funktion, die eine beliebige Spalte einer beliebigen Tabelle liefert?

Mag sein, daß man auch das hinbekommt - aber warum?

Okay, Frage, Gegenfrage, …

Hier ein Beipsiel, was Werte einer Spalte i einer übergebenen Tabelle liefert - zu mehr hab ich grad keine Zeit.

test=# select * from foo;
 i
----
 99
 66
(2 rows)

Time: 0.940 ms
test=*# create or replace function f_demo(text) returns setof int as $$declare sql text;rec record;begin sql:='select i from ' || $1;for rec in execute sql loop return next rec.i; end loop; end;$$ language plpgsql;
CREATE FUNCTION
Time: 2.464 ms
test=*# select * from f_demo('foo');
 f_demo
--------
     99
     66
(2 rows)
[/code ]


Andreas

Nun, da man wohl in einem Skript nicht die Möglichkeit hat das Vorhandensein einer Spalte zu überprüfen, wollte ich mit dieser Funktion eben dies überprüfen und bei Bedarf die Spalte der Tabelle hinzufügen.
Ich komme von einem SQL-Dialekt bei dem dies (auf simpelste Art & Weise) möglich war und dachte, dass Postgres dies sicherlich auch ermöglichen würde, aber das Thema Skripte scheint wohl eher stiefmütterlich behandelt zu sein.
Danke erstmal für deine Mühe, mal sehen ob ich was draus machen kann, wenn nicht, dann werde ich wohl besser die Sache per Programm angehen, es sollte ja eigentlich nur als ad hoc/Übergangslösung eingesetzt werden.

Doch, doch, das geht.

wollte ich mit dieser Funktion eben dies überprüfen und bei Bedarf die Spalte der Tabelle hinzufügen.
Ich komme von einem SQL-Dialekt bei dem dies (auf simpelste Art & Weise) möglich war und dachte, dass Postgres dies sicherlich auch ermöglichen würde, aber das Thema Skripte scheint wohl eher stiefmütterlich behandelt zu sein.

Der genrische SQL-Weg dazu ist wohl, das information_schema zu befragen. Das geht auch in PG.
Allerdings hat SQL keine Konstrukte wie IF … THEN oder so. Von daher wäre es interessant zu erfahren, aus welcher Ecke Du genau kommst.


Danke erstmal für deine Mühe, mal sehen ob ich was draus machen kann, wenn nicht, dann werde ich wohl besser die Sache per Programm angehen, es sollte ja eigentlich nur als ad hoc/Übergangslösung eingesetzt werden.

Wenn Du es wirklich in plain SQL willst, dann könntest Du mit einem Script dynamisch ein weiteres erzeugen und dieses dann anschließend ausführen. Ich zeig Dir das mal, kommentarlos:

test=# \d foo
      Table "public.foo"
 Column |  Type   | Modifiers
--------+---------+-----------
 i      | integer |

test=# select case when count(1) = 1 then '--do nothing' else 'alter table foo add column y int;' end from (select case when attname in ('y') then 'ja' else 'nein' end as vorhanden from pg_attribute where attrelid = 'public.foo'::regclass ) foo where foo.vorhanden = 'ja';
               case
-----------------------------------
 alter table foo add column y int;
(1 row)

Time: 2.680 ms
test=*# alter table foo add column y int;
ALTER TABLE
Time: 2.209 ms
test=*# select case when count(1) = 1 then '--do nothing' else 'alter table foo add column y int;' end from (select case when attname in ('y') then 'ja' else 'nein' end as vorhanden from pg_attribute where attrelid = 'public.foo'::regclass ) foo where foo.vorhanden = 'ja';
     case
--------------
 --do nothing
(1 row)

Andreas

Hallo CUBa42,
guck dir mal Kapitel 43. System Catalogs in der Doku an: PostgreSQL: Documentation: Manuals: PostgreSQL 8.2: System Catalogs

Ich hatte ein aehnliches Problem und es wie folgt geloest:

Die Daten Tabellen liegen im Schema data. Ueber die System Catalogs
werden zwei Views angelegt, die a) alle Tabellen und b) alle Spalten der Datentabellen enthalten:

create or replace view data_info.data_tables as
  select ns.nspname as "schema",
         c.relname  as tablename,
         c.oid      as table_oid
    from pg_namespace ns
    left join pg_class c on ns.oid = c.relnamespace
    where ns.nspname = 'data'
      and c.relkind = 'r'
--    order by tablename
;
create or replace view data_info.data_vars as
  select t.schema,
         t.tablename,
         a.attname as varname,
         a.attnum as attrnum,
         case
           when pt.typname = 'float8'  then 'double'
           when pt.typname = 'varchar' then 'varchar(' || a.atttypmod || ')'
           else  pg_catalog.format_type(a.atttypid, a.atttypmod)
         end as "type",
         pt.typname as pg_type,
         case
           when pt.typname = 'int4'   then true
           when pt.typname = 'float8' then true
           when pt.typname = 'numeric' then true
           when pt.typname = 'int2'   then true
           when pt.typname = 'int8'   then true
           when pt.typname = 'float4' then true
           else false
         end as is_numeric,
         d.description as label_de
    from data_info.data_tables t
      left join pg_attribute a  on t.table_oid = a.attrelid
      left join pg_type      pt on a.atttypid = pt.oid
      left join pg_description d
        on ( t.table_oid = d.objoid and a.attnum = d.objsubid)
    where a.attnum > 0
      and not a.attisdropped
--    order by t.tablename,a.attnum
;

Deine Funktion koennte nun wie folgt aussehen (ungetestet):

create or replace function test_col( t_name name, v_name name )
  returns text as
$test_col$
declare
  found name;
begin
  execute
    'select varname from data_info.data_vars'                        
       || ' where tablename = ' || quote_literal(t_name)
       || '   and varname   = '  || quote_literal(v_name)
    into found; 

  if found is not null then
    return 'column exists';
  end if;

  -- perform 'alter table ...' 

  return 'column added';
end;
$test_col$ language plpgsql;

select test_col('foo_tname','bar_colname');

Ob das mit Alter Table klappt weiss ich nicht, aber siehe noch
Example 37-1. Exceptions with UPDATE/INSERT in der Online-Doku.

Gruesse
I.

Vielen Dank für dein lehrreiches Beispiel!