Creating a file for download

[lozdown]

I have a database of addresses I want to format as VCard in a downloadable file.
I can create the VCard text data but I don't see how to use sqlpage to create a download link (ideally streamed like the csv component as the file could be large)

[lovasoa]

There is no way yet to make it streaming, but you could use a normal link with a data url:

select 'button' as component;

set vcard = 'BEGIN:VCARD
VERSION:3.0
FN:John Doe
N:Doe;John
EMAIL:[email protected]
TEL:+1234567890
END:VCARD';

select 'john doe' as title,
  'data:text/vcard,' ||
    sqlpage.url_encode($vcard) as link,
  'John Doe.vcf' as download;

[lovasoa]

And if you want to avoid the data url and make a standalone download link, you can use shell-empty:

select 'http_header' as component,
    'text/vcard' as "Content-Type",
    'attachment; filename="John Doe.vcf"' as "Content-Disposition";

select 
    'shell-empty' as component,
    'BEGIN:VCARD
VERSION:3.0
FN:John Doe
N:Doe;John
EMAIL:[email protected]
TEL:+1234567890
END:VCARD' as html;

[lozdown]

Thanks for your suggestions which I have used in the code below.

select 'http_header' as component,
    'text/vcard' as "Content-Type",
    'attachment; filename="vcf_addresses.vcf"' as "Content-Disposition";

select 'shell-empty' as component;

select string_agg(
    'BEGIN:VCARD
VERSION:3.0
FN:' || 
coalesce(first_name,'') || ' ' || 
coalesce(surname,'') || '
N:' || 
coalesce(surname,'') || ';' ||
coalesce(first_name,'') || '
EMAIL:' || 
coalesce(email_addr1,'') ||'
TEL:' || 
coalesce(mobile_tel_num,'') || '
END:VCARD','
')
FROM addresses;

This mostly creates what I want (a concentated list of VCARDs) but I cannot eliminate the html formatting code that sqlpage generates. How can I do this?

<div class="card my-2 "  >
    <div class="card-body">
        <div class="table-responsive" >
            <table class="table 
            ">
            
                        <thead>
                        <tr>
                                <th class="string_agg">string_agg</th>
                        </tr>
                        </thead>
                    <tbody class="table-tbody list">

                    <tr class=" " ><td class="string_agg align-middle">BEGIN:VCARD
VERSION:3.0
FN:------------------
N:-------------;---------------
EMAIL:--------------------
TEL:------------------
END:VCARD
BEGIN:VCARD
VERSION:3.0
FN:-----------------------------
N:---------------;-----------------
EMAIL:---------------------------
TEL:
END:VCARD
BEGIN:VCARD
VERSION:3.0
.......

[lovasoa]

you have two SQL queries instead of just one. Jean single escual croary that returns both shell empty and the vcard in a column named 'html'.

[lovasoa]

select 'http_header' as component,
    'text/vcard' as "Content-Type",
    'attachment; filename="vcf_addresses.vcf"' as "Content-Disposition";

select 'shell-empty' as component,
string_agg(
    'BEGIN:VCARD
VERSION:3.0
FN:' || 
coalesce(first_name,'') || ' ' || 
coalesce(surname,'') || '
N:' || 
coalesce(surname,'') || ';' ||
coalesce(first_name,'') || '
EMAIL:' || 
coalesce(email_addr1,'') ||'
TEL:' || 
coalesce(mobile_tel_num,'') || '
END:VCARD','
') as html
FROM addresses;