using a date type is a postgrsql SET statement

user_68746 0
Viewed 68

I am using pgadmin and postgrsql 9.6.8

I am not sold on doing it this way, but it is the only way I have found so far. I am just wanting to be able to use a replaceable variable or a parameter in a query in pgAdmin 4

This is the only way I have found to do it. It works for the branchToUse but is failing when I try and pass the date.

Here is the query:

    SET search1.branchToUse = 'EUR';
SET search1.dateToUse = '2019-6-20';
select si.unique_id_no,si.customer_no, stop_name,si.shipment_type,stop_address,stop_city,assigned_driver,actual_driver,
si.datetime_created as "stop DT created",si.datetime_updated as "stop DT updated",rh.route_code,rh.datetime_last_updated,rh.updated_datetime from distribution_stop_information si
join distribution_route_headers as rh on si.route_code = rh.route_code

where (si.company_no =11 ) and (rh.company_no = 11)  and
si.created_by='DI*' and
(si.route_date = current_setting('search1.dateToUse') and rh.route_date = current_setting('search1.dateToUse') ) and
(si.branch_id = current_setting('search1.branchToUse') and rh.branch_id = current_setting('search1.branchToUse'))
order by  stop_address;

I am getting the following error - I am not sure how to do the requested cast:

ERROR: operator does not exist: date = text LINE 9: (si.route_date = current_setting('search1.dateToUse') and rh... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

postgresql sql
2 Answers

You should cast the text (containing a date) to date

set search1.dateToUse = '2019-6-20';
select current_setting('search1.dateToUse')::date;

 current_setting 
-----------------
 2019-06-20
(1 row)
user_19957380

You could also do this using a function:

CREATE OR REPLACE FUNCTION function_name(dateToUse date, branchToUse text)
RETURNS TABLE( unique_id_no          <data_type>
             , customer_no           <data_type>
             , stop_name             <data_type>
             , shipment_type         <data_type>
             , stop_address          <data_type>
             , stop_city             <data_type>
             , assigned_driver       <data_type>
             , actual_driver         <data_type>
             , "stop DT created"     <data_type>
             , "stop DT updated"     <data_type>
             , route_code            <data_type>
             , datetime_last_updated <data_type>
             , updated_datetime      <data_type>
             )
AS $$
DECLARE
BEGIN
    RETURN QUERY
    SELECT  si.unique_id_no
            , si.customer_no
            , stop_name
            , si.shipment_type
            , stop_address
            , stop_city
            , assigned_driver
            , actual_driver
            , si.datetime_created as "stop DT created"
            , si.datetime_updated as "stop DT updated"
            , rh.route_code
            , rh.datetime_last_updated
            , rh.updated_datetime 
    FROM distribution_stop_information si
        JOIN distribution_route_headers rh ON si.route_code = rh.route_code
    WHERE (si.company_no =11 )
        AND (rh.company_no = 11)
        AND si.created_by = 'DI*'
        AND (si.route_date = dateToUse AND rh.route_date = dateToUse)
        AND (si.branch_id = branchToUse AND rh.branch_id = branchToUse)
    ORDER BY stop_address
    ;
END;
$$

Example call: SELECT * FROM function_name('2019-06-20'::date, 'EUR');

user_93518170
Related