How to pass timestamps to a function and retrieve resulting rows

Question

I have created custom data type. In that I have given alias name of the one field. you will get that in body of the function below.

create type voucher as (
   ori numeric, RECEIPT_NO numeric
 , receipt_date timestamp with time zone, reg_no character varying
 , patient_name character varying, tot_refund_bill_amount double precision
 , username character varying );

Thea above statement completes successfully.
Then I want to create a function:

create or replace function billing.voucher_receipt (in_from_date timestamp with time zone, in_to_date timestamp with time zone)
returns setof voucher as $$

declare
 out_put voucher%rowtype;

 begin
 return query(select C.receipt_no as ori ,A.RECEIPT_NO, receipt_date , A.reg_no, patient_name, tot_refund_bill_amount, username 
from billing.tran_counter_receipt as a inner join mas_user as b on a.ent_by=b.uid AND cash_book='REFUND' 
INNER JOIN billing.tran_BILL AS C ON C.REG_NO=A.REG_NO AND C.CASH_BOOK='GCASH' where receipt_date>=in_from_date and receipt_date<=in_to_date);

end;$$

LANGUAGE plpgsql

Executes without problem.
But when I call it with input like this:

select * from voucher_receipt ('2014-09-25 11:42:44.298346+05:30'
                             , '2014-09-29 11:03:47.573049+05:30')

it shows an error:

ERROR:  function voucher_receipt(unknown, unknown) does not exist
LINE 1: select * from voucher_receipt ('2014-09-25 11:42:44.298346+0...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Can any one help me out from this?

What do you get for `SHOW search_path;` in the same session? Also, your version of Postgres, please. — Erwin Brandstetter, Dec 11 '14 at 12:19
I wanted to get data by searching in b/w dates. to form the report. And version is 9.3. I'll get data of patients refund has been done. firstly they has paid advance money. I'll get the data of refund paid to the paitents — suchith shivali, Dec 11 '14 at 15:45

score 2 · Answer 1 · edited May 23 '17 at 11:50

Explain error

You created your function in the schema billing with:

create or replace function billing.voucher_receipt( ...

Then you call without schema-qualification:

select * from voucher_receipt ( ...

This only works while your current setting for search_path includes the schema billing.

Better function

You don't need to create a composite type. Unless you need the same type in multiple places just use RETURNS TABLE to define the return type in the function:

CREATE OR REPLACE FUNCTION billing.voucher_receipt (_from timestamptz
                                                  , _to   timestamptz)
  RETURNS TABLE (
     ori numeric
   , receipt_no numeric
   , receipt_date timestamptz
   , reg_no varchar
   , patient_name varchar
   , tot_refund_bill_amount float8
   , username varchar) AS
$func$
BEGIN
   RETURN QUERY
   SELECT b.receipt_no -- AS ori
        , cr.RECEIPT_NO
        , ??.receipt_date
        , cr.reg_no
        , ??.patient_name
        , ??.tot_refund_bill_amount
        , ??.username
   FROM   billing.tran_counter_receipt cr
   JOIN   billing.tran_bill            b USING (reg_no)
   JOIN   mas_user                     u ON u.uid = cr.ent_by
   WHERE  ??.receipt_date >= _from
   AND    ??.receipt_date <= _to
   AND    b.CASH_BOOK = 'GCASH'
   AND    ??.cash_book = 'REFUND'
END
$func$ LANGUAGE plpgsql;

Notes

Don't call your parameters "date" while they are actually timestamptz.
RETURN QUERY does not require parentheses.
No need for ~~DECLARE out_put voucher%rowtype;~~ at all.
Your format was inconsistent and messy. That ruins readability and that's also where bugs can hide.
This could just as well be a simple SQL function.
Column names in RETURNS TABLE are visible in the function body almost everywhere. table-qualify columns in your query to avoid ambiguities (and errors). Replace all ??. I left in the code, where information was missing.
Output column names are superseded by names in the RETURNS declaration. So AS ori in the SELECT list is just documentation in this case.
Why schema-qualify billing.tran_bill but not mas_user?

I understood your point, but last point in note, in mas_user I'm retrieving only one date its in public. — suchith shivali, Dec 11 '14 at 15:54
@suchithshivali: `public` is a schema like any other schema. If it's in the `search_path` everywhere, it's ok to omit, but while presenting the case to the general public, you must provide the information .. in your question. — Erwin Brandstetter, Dec 11 '14 at 15:59

How to pass timestamps to a function and retrieve resulting rows

1 Answers1

Explain error

Better function

Notes