-2

I am unable to write a normal select procedure in PostgreSQL. I have good experience in SQL Server. But Postgres seems like entirely a different planet.

Can anybody help me how to write this statement in Postgres?

create proc procedurename @somedate date
 begin
select * from sometable where date>= @somedate
end

and I can execute it like

exec procedurename 2017-05-02

I tried to write a stored procedure which returns a table with something like

    Create or replace function hrms.salary() returns table(
lastmodified    timestamp without time zone ,
employeeid  integer ,
insuranceno character varying   ,
uanno   character varying   ,
basic   integer ,
hra numeric ,
conveyence  integer ,
fixedallowance  integer ,
epf numeric ,
eps numeric ,
esic    numeric ,
bankname    character varying   ,
ifsccode    character varying   ,
bankaccount character varying   ,
totalsalary integer 
) as
 $Body$
Begin
with pivotchart as(
SELECT * FROM hrms.crosstab(
  $$ SELECT employeeid,today,count(employeeid) as countid 
  FROM hrms.timesheet 
  where dated>='2017-09-01'
  and dated< '2017-09-01'::timestamp+ '1 MONTH'::INTERVAL
  group by employeeid,today $$,
  $$ select distinct today 
  from hrms.timesheet $$
)as
finalresult(employeeid int,leave int,present int,absent int, holiday int)
),
groupedresults as(
select employeeid ,
sum(present) as present,
sum(absent) as absent,
sum(holiday) as holiday,
sum(leave) as leave
from pivotchart
group by employeeid
),
numberofdays as(
SELECT  extract(day from DATE_TRUNC('month', now()::timestamptz) + '1 MONTH'::INTERVAL
      - DATE_TRUNC('month', now()::timestamptz))::int as interval
),
joinsofvalue as(
select emp.firstname,
emp.lastname,
emp.fathername,
grp.employeeid,
grp.Present,
grp.HOLIDAY,
grp.ABSENT,
grp.LEAVE,
sal.basic as Totalbasic,
(sal.basic)/(select interval from numberofdays )* ((select interval from numberofdays)-(absent))as calculatedbasic,
sal.hra as totalHRA,
(sal.hra)/(select interval from numberofdays )* ((select interval from numberofdays)-(absent))as calculatedHRA,
sal.conveyence as totalconveyence,
(sal.conveyence)/(select interval from numberofdays )* ((select interval from numberofdays)-(absent))as calculatedconveyence,
sal.fixedallowance as totalfixedallowance,
(sal.fixedallowance)/(select interval from numberofdays )* ((select interval from numberofdays)-(absent))as calculatedFA,
sal.epf as totalemployeeprovidendfund,
(sal.epf)/(select interval from numberofdays )* ((select interval from numberofdays)-(absent))as calculatedEPF,
sal.eps as totalemployeepensionscheme,
(sal.eps)/(select interval from numberofdays )* ((select interval from numberofdays)-(absent))as calculatedEPS,
sal.esic as totalemployeestateinsurance,
(sal.esic)/(select interval from numberofdays )* ((select interval from numberofdays)-(absent))as calculatedESIC,
sal.totalsalary as GrossSalary,
(sal.totalsalary)/(select interval from numberofdays )* ((select interval from numberofdays)-(absent))as NETSalary
from (groupedresults as grp
right outer join 
hrms.employee as emp
on 
grp.employeeid = emp.employeeid)
left outer join
hrms.salarystructure as sal
on emp.employeeid=sal.employeeid
)
select * from joinsofvalue;
end;
$Body$
language plpgsql;

it creates a function without any issues.

Query returned successfully with no result in 38 msec.

But then again when I try to query it. using

select * from hrms.salary()

it gives me an error stating

ERROR:  column reference "employeeid" is ambiguous
LINE 14: select employeeid ,
                ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.

There is no documentation to solve this however I know for sure that everything after the with statement works just fine. In fact I have created a view with same query and it works. But I need to include a parameter and that is the reason I need it.

closed as off-topic by McNets, dezso, Marco, hot2use, James Anderson Sep 12 '17 at 11:01

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Marco, hot2use, James Anderson
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 4
    The manual would be a good start. You can't expect Microsoft SQL Server syntax to work on another product, be it PostgreSQL, Oracle, or anything else. – Craig Ringer Sep 12 '17 at 8:20
3

Postgres has no procedures. You need a function that returns a result:

create function some_function(p_somedate date)
  returns setof sometable
as
$$
select * 
from sometable 
where date >= p_somedate;
$$
language sql;

Then run:

select *
from some_function(date '2017-11-01');

Please also have a look at the examples in the manual here and here

Edit after the real code was shown:

You are using language plpgsql which is different then a plain SQL function (using language sql as in my example).

As documented in the manual a PL/pgSQL function needs to use return query if it wants to return a result.

A plain SQL function can simply write a SELECT statement.


If you want to stick with PL/pgSQL, you need something like this:

create or replace function hrms.salary() 
   returns table(...) as
$Body$
Begin
  RETURN QUERY
  with pivotchart as(
    ...
  ),
  groupedresults as (
    ....
  ),
  numberofdays as(
     ...
  ),
  joinsofvalue as(
     ...
  )
  select * 
  from joinsofvalue;
end;
$Body$
language plpgsql;

A plain SQL function is typically faster then the equivalent PL/pgSQL function using RETURN QUERY.

In that case you have no BEGIN ... END and you simply put the query into the function body and change the language to sql:

create or replace function hrms.salary() 
  returns table(...) as
$Body$
  -- no BEGIN here, no RETURN QUERY required
  -- just write the query 
  with pivotchart as(
    ...
  ),
  groupedresults as (
    ....
  ),
  numberofdays as(
     ...
  ),
  joinsofvalue as(
     ...
  )
  select * 
  from joinsofvalue;
$Body$
language sql;

As for your error:

ERROR: column reference "employeeid" is ambiguous

The detail tells you exactly what the cause of that error is:

DETAIL: It could refer to either a PL/pgSQL variable or a table column.

You have a parameter named employeeid and you have a column named employeeid. In the query:

  groupedresults as (
    select employeeid ,
           sum(present) as present,
           sum(absent) as absent,
           sum(holiday) as holiday,
           sum(leave) as leave
    from pivotchart
    group by employeeid
  )

employeeid could reference the table's column or the parameter.

More details about name resolution are available in the manual

The usual naming convention (as I have used in my first example) is to prefix the parameters with something so that there is no name clash.

You can also prefix the column name with the table to avoid the name clash:

  groupedresults as (
    select p.employeeid ,
           sum(p.present) as present,
           sum(p.absent) as absent,
           sum(p.holiday) as holiday,
           sum(p.leave) as leave
    from pivotchart as p
    group by p.employeeid
  )

But it's typically better to use parameter and variable names that do not conflict with column names.

Not the answer you're looking for? Browse other questions tagged or ask your own question.