How to call a database Function from the code

Asked

Viewed 341 times

2

would like a help,

i have the following Function that is in the bank postgres

CREATE OR REPLACE FUNCTION public.get_id(campo character varying)
  RETURNS integer AS
$BODY$
DECLARE
  _id integer;
BEGIN
  select into _id vlr_corrente from system_generators where nome = $1;
  if _id is null 
  then
   insert into system_generators(nome, vlr_corrente) Values(campo, 1);
   select into _id vlr_corrente from system_generators where nome = $1;
   update system_generators set vlr_corrente = vlr_corrente + 1 where nome = $1;
  else
   select into _id vlr_corrente from system_generators where nome = $1;
   update system_generators set vlr_corrente = vlr_corrente + 1 where nome = $1;
  end if;

  return _id;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.get_id(character varying)
  OWNER TO postgres;

This Function generates the primary keys for me, to generate the primary keys, I just have to call her: select public.get_id('VENDA'), she returns me a unique number.

So far so good, but as I call the Function that is in the bank, via code line using EntityFramework/C#/MVC and returning the number.

Or if possible I can generate the primary keys from the same code, without calling Function, already tried to use:

[Key,DatabaseGenerated(DatabaseGeneratedOption.Identity)]

        public int id { get; set; }

But not for sure, the following message appears: "null value in column "id" violates not-null Constraint"

Note: I can not do inside the tables, auto increment.

Thanks for your help !!

c# asp.net-mvc postgresql

1 answer

1

The same way you do SELECT. 

var query = "SELECT functionName() as Result FROM Dual";
var result = con.ExecuteScalar(query);

Browser other questions tagged c# asp.net-mvc postgresql

You are not signed in. Login or sign up in order to post.