Export WooCommerce Products (Postgres)

export_products.sql

DROP FUNCTION thumbnail_for_post(_postid numeric);


CREATE FUNCTION thumbnail_for_post(_postid numeric) RETURNS text
AS $$
#print_strict_params on
DECLARE
thumbnail text;
BEGIN
    SELECT meta_value  INTO thumbnail 
    	FROM wp_postmeta WHERE post_id = 
		(SELECT id FROM wp_posts WHERE id = (SELECT to_number(meta_value, '9999')
	       FROM wp_postmeta WHERE post_id=_postid AND meta_key='_thumbnail_id'))
	       AND meta_key='_wp_attached_file';
    RETURN thumbnail;
END
$$ LANGUAGE plpgsql;

DROP FUNCTION price_for_post(_postid numeric);


CREATE FUNCTION price_for_post(_postid numeric) RETURNS text
AS $$
#print_strict_params on
DECLARE
price text;
BEGIN
    SELECT meta_value INTO price 
    	FROM wp_postmeta WHERE post_id =_postid AND meta_key='_price';
    RETURN price;
END
$$ LANGUAGE plpgsql;



select post_content as meta_description, post_title as name, post_excerpt as description, post_name as slug, thumbnail_for_post(id) as thumbnail, price_for_post(id) as price from wp_posts where 
	post_type='product';