0

I'mn new in PostgreSQL here is my function which I created. but it does not run successfully because something I'm doing wrong. please, can anyone help me in this?

CREATE OR REPLACE FUNCTION Insert_Tracking_Detail1()
RETURNS setof temp_tracking AS
$$
BEGIN
DROP TABLE IF EXISTS temp_tracking;
CREATE TEMPORARY TABLE temp_tracking
(
    TrackingID int4,
    Url varchar(900),
    UserAgent varchar(300),
    createdon TIMESTAMP,
    ipaddress varchar(50)
) 
ON COMMIT PRESERVE ROWS;
INSERT INTO temp_tracking (TrackingID,Url,UserAgent,createdon,ipaddress)
select TrackingID,Url,UserAgent,createdon,ipaddress from tracking

RETURN select * from temp_tracking;
END
$$
LANGUAGE plpgsql;
  • 1
    What is the error you get? And why don't you just run select TrackingID,Url,UserAgent,createdon,ipaddress from tracking in the function? The temporary table seems pretty useless. – a_horse_with_no_name Nov 20 '17 at 9:52
  • error is "RETURN select * from temp_tracking; – Nayeem Mansoori Nov 20 '17 at 10:01
  • I'm using temp table in this because i want to move the data from tacking to tarcking _details table. And tracking table exist url and tracking_detail table exist url parameters this is not a full function this is in progress – Nayeem Mansoori Nov 20 '17 at 10:02
  • For PL/pgSQL you need RETURN QUERY: postgresql.org/docs/current/static/… – a_horse_with_no_name Nov 20 '17 at 10:32
  • 1
    If you want to move the data, then simply use insert into tracking_details select * from tracking you usually don't need temporary tables in Postgres e.g. to avoid locking as you do in SQL Server. – a_horse_with_no_name Nov 20 '17 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.