Syntax of Postgresql

Hello,

This is my first ever function in Postgressql.  

I have 2 variables date1 and date2, Can I use them in my insert query directly like this in my function?

insert into abc (select distinct member_id, date as yearmo,to_date(Date1, 'MM-DD-YYYY') .....
where effdt <= Date1  and enddt >= Date2)

Open in new window

perlwhiteAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
Edit: I just re-read the question and see that you're using a function, so I've removed my comment (it doesn't apply to that).
0
zappafan2k2Commented:
Are you trying to create a function withing Postgres that will insert rows into table abc?  What exactly are you trying to do here?
0
PortletPaulfreelancerCommented:
I'm a little confused by the question. The only function in the SQL provided is to_date()

do you mean:
"I have 2 variables date1 and date2, can I use them in a where clause?"

PostgreSQL only allows for variables within PL/pgSQL code, so there is no simple way to use variables in the where clause of your insert code.

Perhaps you could elaborate on what you are trying to achieve.

reference:
Practical PostgreSQL
Using Variables
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

perlwhiteAuthor Commented:
I apologize for not being clear.
Here is my function.

CREATE OR REPLACE FUNCTION Check returns void AS $$

DECLARE
    startDate=date;
	Date1=date;
	Date2=date;
	newDate=date;
		
BEGIN
	
	
    for i in 1..13 LOOP
	startDate:= SELECT to_date(lastdate::date, 'MM-DD-YYYY') FROM setup;
	formatdate2:= to_date(startDate::date,'YYYYMM');
      IF i = 1 THEN
			formateDate1 := select (startDate - INTERVAL '11 months');
insert into abc (select distinct member_id, date as yearmo,to_date(Date1, 'MM-DD-YYYY') .....
where effdt <= Date1  and enddt >= Date2)
			 
--repeat this 13 times


     END IF;




	  
    END LOOP;
    
END
$$ language 'plpgsql';

Open in new window


Can I use the delcared variables like these above?  Also, can I call another function which will call the insert query within this function?

Here is what I am trying to do-  I am trying to enter data for today's date and data for last 11 months and data for the next month.
0
earth man2Commented:
this will create a function that has correct psql syntax.  I can't quite follow your requirements though


CREATE OR REPLACE FUNCTION stuff( Date1 date, Date2 date ) returns void AS $$
DECLARE
  startDate date;
  formatDate1 date;
  i integer;
BEGIN      
  for i in 1..13 LOOP
    SELECT to_date( lastdate, 'MM-DD-YYYY') into startDate FROM setup;
    IF i = 1 THEN
      formatDate1 := startDate - INTERVAL '11 months';
      insert into abc select distinct member_id, "date" as yearmo, Date1 from tablex      
          where effdt <= Date1  and enddt >= Date2;            
    END IF;       
  END LOOP;
END;
$$ language plpgsql;
0
earth man2Commented:
postgres=#
select (generate_series)::date from generate_series(current_date - '11 months'::interval, current_date + '1 month'::interval, '1 month'::interval);

Open in new window

generate_series
-----------------
 2012-11-19
 2012-12-19
 2013-01-19
 2013-02-19
 2013-03-19
 2013-04-19
 2013-05-19
 2013-06-19
 2013-07-19
 2013-08-19
 2013-09-19
 2013-10-19
 2013-11-19
(13 rows)


postgres=#
0
earth man2Commented:
postgres=#
 
select (generate_series)::date as som, (((generate_series)::date + '1
 month'::interval) - '1 day'::interval)::date as eom from generate_series(date_t
runc('month',current_date) - '11 months'::interval, date_trunc('month',current_d
ate) + '1 month'::interval, '1 month'::interval);

Open in new window


    som     |    eom
------------+------------
 2012-11-01 | 2012-11-30
 2012-12-01 | 2012-12-31
 2013-01-01 | 2013-01-31
 2013-02-01 | 2013-02-28
 2013-03-01 | 2013-03-31
 2013-04-01 | 2013-04-30
 2013-05-01 | 2013-05-31
 2013-06-01 | 2013-06-30
 2013-07-01 | 2013-07-31
 2013-08-01 | 2013-08-31
 2013-09-01 | 2013-09-30
 2013-10-01 | 2013-10-31
 2013-11-01 | 2013-11-30
(13 rows)
0
perlwhiteAuthor Commented:
@earthman2,

Please excuse my ignorance. why is  (Date1 date, Date2 date) specified here in function definition?
CREATE OR REPLACE FUNCTION stuff( Date1 date, Date2 date ) returns void AS $$

Open in new window


Also,

I did not get it still, can I call my long insert query as a different function since I am calling them 13 times and do not want to make my main function unnecessarily long.
0
earth man2Commented:
You did not set date1 or date2 so I put them as arguments as to demonstrate plpgsql syntax.  If you can be more specific as to what you want to acheive we can be more helpful...
0
gwnetCommented:
Maybe this help you :

INSERT INTO abc ( member_id, yearmo, to_date, ...... )

WITH  rec AS (
      SELECT  date1, date2  FROM yourfunction()
) 
SELECT  member_id, date as yearmo, to_date(rec.Date1, 'MM-DD-YYYY') .....  FROM rec
JOIN other_data od ON added::date = rec.date1 ( OR OTHER YOUR CONDITION )
WHERE  od.effdt <= rec.date1  and od.enddt >= rec.date2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.