Stored procedure Postgresql and C# WPF

Question

I want to access postgres by stored procedure.. when I execute function it was successful but when I run it using C# it has an error

:syntax error at or near ")" sqlerror : select * from laporan_kab('32'::varchar,'14'::varchar) as()

My function is:

CREATE OR REPLACE FUNCTION laporan_kab(aprop character varying, atahun character varying)
  RETURNS TABLE (kode_wilda varchar(255), wilayah varchar(255), dda integer, statda integer, lainnya integer) AS
$BODY$

DECLARE
    jml_wilda integer;
    jml_dda integer;
    jml_statda integer;
    jml_lainnya integer;
    nm_wilayah varchar(255);
    i integer;
    aprod varchar(255);

BEGIN

    CREATE TEMP TABLE lap (
    akode_wilda varchar(255),
    awilayah varchar(255),
    adda integer,
    astatda integer,
    alainnya integer
    );

    CREATE TEMP TABLE wilda (
    id serial,
    kd_prop varchar(255),
    kd_kab varchar(255),
    kd_kec varchar(255),
    no_urut integer,
    nm_wilda varchar(255)
    );

    INSERT into wilda(kd_prop,kd_kab,kd_kec,no_urut,nm_wilda) SELECT kd_prop,kd_kab,kd_kec,no_urut,nm_wilda from t_history_wilda where is_active = '1' and kd_prop = aprop and kd_kec = '000' and kd_kab != '00';

    SELECT count(*) INTO jml_wilda from wilda;

    i := 1;
    while(i <= jml_wilda)
    LOOP

        SELECT kd_prop||kd_kab||kd_kec||no_urut INTO aprod from wilda WHERE id = i;

        SELECT count(*) INTO jml_dda from t_publikasi where kd_produsen = aprod and kd_bahan_pustaka ='121' and thn_terbit = atahun;
        SELECT count(*) INTO jml_statda from t_publikasi where kd_produsen = aprod and kd_bahan_pustaka ='122' and thn_terbit = atahun;
        SELECT count(*) INTO jml_lainnya from t_publikasi where kd_produsen = aprod and kd_bahan_pustaka !='121' and kd_bahan_pustaka !='122' and thn_terbit = atahun;
        SELECT nm_wilda INTO nm_wilayah from wilda WHERE id = i;

        INSERT INTO lap values (aprod,nm_wilayah,jml_dda,jml_statda,jml_lainnya);
        i := i + 1;
    END LOOP;

    PERFORM * from lap;
    RETURN QUERY SELECT * from lap ;

END 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION laporan_kab(character varying, character varying) OWNER TO postgres;

And my code in c# :

public DataSet LapPubDa(string kode, string tahun)
    {
        DBConnection odbCon = new DBConnection();
        DBData odbData = new DBData();
        DataSet ds = new DataSet();
        DBParameter akode = new DBParameter();
        string aprop = "aprop";
        akode.Name = aprop;
        akode.Type = NpgsqlTypes.NpgsqlDbType.Varchar;
        akode.Value = kode;
        DBParameter atahun = new DBParameter();
        string btahun = "atahun";
        atahun.Name = btahun;
        atahun.Type = NpgsqlTypes.NpgsqlDbType.Varchar;
        atahun.Value = tahun;
        DBParameter[] param = { akode, atahun };
        string cmd = "laporan_kab";
        odbData = odbCon.ExecuteStoredProcedureCommand(cmd, param);
        return ds;
    }

you are creating temp tables every time you run it when u are deleting them??? — Syeda
– Syeda, Commented Jun 22, 2011 at 13:58

Marius · Accepted Answer · 2011-06-23 09:02:48Z

1

Probably is because of:

select * from laporan_kab('32'::varchar,'14'::varchar) as() 
-- note the missing "as ('x', 'y', 'z')"

I see that you're query returns something:

RETURN QUERY SELECT * from lap ;

So try to add some output parameters:

NpgsqlParameter firstColumn = new NpgsqlParameter("firstcolumn", NpgsqlDbType.Integer);
firstColumn.Direction = ParameterDirection.Output;
command.Parameters.Add(firstColumn);

Check the docs from here for more examples: http://npgsql.projects.postgresql.org/docs/manual/UserManual.html, the section "Using output parameters in a query ".

answered Jun 23, 2011 at 9:02

Marius

3,6473 gold badges31 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Stored procedure Postgresql and C# WPF

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related