Here is the error:

ERROR: [GET_ALL_DATASETS_BY_DATE] syntax error at or near "LANGUAGE"
LINE 3:         LANGUAGE SQL

Here is the code

def PROCEDURE_GET_ALL_DATASETS_BY_DATE(conn):
    print("CREATING [GET_ALL_DATASETS_BY_DATE] PROCEDURE")
    try:
        cursor = conn.cursor()
        cursor.execute(f"""
        ALTER PROCEDURE GET_ALL_DATASETS_BY_DATE(how_many int)
        LANGUAGE SQL
        AS $$
        SELECT U.username, F.File_PATH "Path", Description "Desc", F.Date_Time "Date", F.File_size "Size"
            FROM USERS as U
            INNER JOIN FILES as F
            ON F.UserId = U.User_Id
            ORDER BY F.Date_Time
            limit how_many  
            $$;
        """)
        conn.commit()
    except Exception as e:
        cursor.execute("ROLLBACK")
        print("ERROR: [GET_ALL_DATASETS_BY_DATE] "   str(e))

I feel like I am missing something super simple...Thank you for your time. Any help is appreciated

here is the documentation link to how I've been modelling my procedures.

https://www.postgresql.org/docs/current/sql-createprocedure.html

CodePudding user response：

if you wanna bring the table by using join. You can use the code below.

PostgreSQL is not like MSSQL or TSQL. In MSSQL and TSQL we can use procedure as direct. But In Postgres, if we want to use select processing, we have to specify columns on our query.

Example:

    CREATE OR REPLACE FUNCTION public.myposts(_user_id integer)
 RETURNS TABLE(post_id integer, create_user_id integer, ad_name character varying, topic character varying, content_values text, slug_url character varying)
 LANGUAGE plpgsql
AS $function$
BEGIN
   RETURN QUERY
   select distinct posts.id,posts.create_user_id,users.ad_name,posts.topic,posts.content_values,posts.slug_url from posts 
   inner join users on users.id = posts.create_user_id
   inner join post_receivers on post_receivers.post_id = posts.id
   where post_receivers.user_id = _user_id;
END
$function$
;