Query all the transaction details from a given block using cardano db sync

Question

Can someone help out with how can i query the transaction details from a block using cardano db sync table? I actually need fields like epoch no, date of transaction, stake address, pool and the amount of the given transaction? I was trying it from the delegation table, but i am having problem with finding the epoch no and if the amount for a given stake address is input or an output(-ve for outgoing and +ve for incoming amount for a given stake address).

Answer 1

Efficient manner is join.

select block.id as block_identifier,* from tx left join block on block.id=tx.block_index where tx.hash='\x{hash}';

Make sure you pay attention to not mixing ID fields.

Some variables not accurately named, a quick check should help you though. You'll need to login to your postgres instance and connect using \c cexplorer

Then type \dt+ to get tables. \d+ {tablename}